Thread: Data backup to local duplicate without resetting permissions

What's the easiest way, in PostgreSQL 9.0, to keep a duplicate database on my local machine updated at intervals from
anotheron a remote machine without having to reset permissions after a backup/restore. Also which type of backup? Plain
textor compressed? Data-only or complete shema? Setup duplicate users or can I use a distinct set of users? I would
ideallylike to have the same schemas setup with distinct users then simply have a data-only backup from the remote
serverwhich I can restore to the local machine without resetting privileges/permissions. 

gvim

On 12/23/2010 08:55 AM, gvim wrote:
> What's the easiest way, in PostgreSQL 9.0, to keep a duplicate database
> on my local machine updated at intervals from another on a remote
> machine without having to reset permissions after a backup/restore. Also
> which type of backup? Plain text or compressed? Data-only or complete
> shema? Setup duplicate users or can I use a distinct set of users? I
> would ideally like to have the same schemas setup with distinct users
> then simply have a data-only backup from the remote server which I can
> restore to the local machine without resetting privileges/permissions.
>
> gvim
>

This is probably is going to need more information but in the meantime.

pg_dump switches -a data only -O no-owner(do not restore object
ownership) -x no-privileges(do not dump GRANT/REVOKE commands)

I am not sure what you mean by update on the local machine. If you mean
start from scratch when you run restore then the above switches will
help. If you mean only restore differences between the local and remote
machines then you will need to go down a different path

--
Adrian Klaver
adrian.klaver@gmail.com

On 2010-12-23, gvim <gvimrc@gmail.com> wrote:
> What's the easiest way, in PostgreSQL 9.0, to keep a duplicate
> database on my local machine updated at intervals from another on a
> remote machine without having to reset permissions after a
> backup/restore.

create roles matching the names of the remote roles and grant those
roles to your local users.

--
⚂⚃ 100% natural