Re: How to transfer databases form one server to other

On 1/26/20 10:56 PM, Andrus wrote:
> Hi!
> 
>>> 3. Manually edit role definitions to delete role postgres since it 
>>> exists
>>> in new server.
>> No need, it will throw a harmless error message and continue on.
> 
> By my knowledge, pgAdmin executes script in single transaction and rolls it
> back on error.
> Should psql used or is there some option in pgadmin.

There are options on pgAdmin:
https://www.pgadmin.org/docs/pgadmin4/4.17/restore_dialog.html
See:

Single transaction
Exit on error

Can't remember if you are taking a custom format backup or plain text. 
If plain text you can use psql. If custom then will need to use pg_restore.

> 
>>> To repeat transfer after testing:
>>>
>>> 1. Delete restored databases.
>>> 2. Delete imported roles in new server
>> That will probaly not end well. I'm guessing there are objects that 
>> have a
>> dependency on the the roles.
> 
> If imported databases are dropped before, there will be hopefully no
> dependencies.

Roles are global, dependencies can be local to a database. The issue is 
if objects in a restored database depend on roles that no longer exist 
in the global context.

> 
> Andrus.


-- 
Adrian Klaver
adrian.klaver@aklaver.com