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(dot)klaver(at)aklaver(dot)com