Login and Permission Tips and Tricks in using pg_dump and pg_restore

[Shau Phang]

Pg_dump and pg_restore are commonly used in data movement between PostgreSQL servers. Pg_dump and pg_restore assume super_user is being used, and when you are migrating data from on-premise to the cloud, there are some tips and tricks that will help you in executing pg_dump and pg_restore.

1.      Use --role=rolename in pg_dump

There are cases when the login used to connect to PostgreSQL instance and the login used to run pg_dump has different privileges. This is very common when you move between databases.

The solution is to add –role=rolename switch in the pg_dump command. This switch will run SET ROLE rolename command after connecting to the database, in other words, this command sets the current SQL session to role_name.

One thing to note, the role in --role should be a member of the role used in --username.

For example, I’m going to use login1 to connect to the PostgreSQL host but run restore under login2. Before I run pg_dump, I’m going to create a role as ‘login2’ if it hasn’t existed.

CREATE ROLE login2

GRANT login2 to login1;

To dump:

pg_dump -v --no-owner --host=localhost --port=5432 --username=login1 --role=login2 --dbname=movies D:\movies.dump

2.      Use --role=rolename in pg_restore

Similarly, you can also use --role=rolename in pg_restore when login used to connect to PostgreSQL instance and the login used to run pg_restore has different privileges.

This switch will run SET ROLE rolename command after connecting to the database, in other words, this command sets the current SQL session to role_name.

One thing to note, the role in --role should be a member of the role used in --username.

For example, I’m going to use login1 to connect to the PostgreSQL host but run restore under login2. Before I run pg_restore, I’m going to create a role as ‘login2’ if it hasn’t existed.

CREATE ROLE login2

GRANT login2 to login1;

To restore:

pg_restore -v --no-owner --host= mydemoserver.postgres.database.azure.com --port=5432 --username=login1@mydemoserver --role=login2 --dbname=movies D:\movies.dump

To assign database owner to login2, simply run alter database:-

ALTER DATABASE movies OWNER TO login2;

3.      Use --use-set-session-authorization in pg_dump

In some use cases, you would want the application or the user to authenticate through web app through JDBC and access the database. If there is a scenario to issue a dump through authenticated user within the webapp, you can use SET SESSION AUTHORIZATION to proxy the user on a JDBC session.

--use-set-session-authorization issues SET SESSION AUTHORIZATION commands instead of ALTER ONWER commands to determine object ownership. Please note, a dump using SET SESSION AUTHORIZATION will require superuser privileges to restore correctly.

See more on SET SESSION AUTHORIZATION here.

4.      Use --no-owner in pg_restore

By default, pg_restore issues ALTER OWNER or SET SESSION AUTHORIZATION statements to set ownership of created schema elements. Using --no-owner in pg_restore, the username you specified in --username will own all the created objects which is helpful when you don’t have super_user login permission.

For example:-

pg_restore -v --no-owner --host=mydemoserver.postgres.database.azure.com --port=5432 --username=mylogin@mydemoserver --dbname=mypgsqldb testdb.dump

For complete step by step details in running dump and restore, please refer to:

https://docs.microsoft.com/en-us/azure/postgresql/howto-migrate-using-dump-and-restore

For more information on performance optimization in doing pg_dump and pg_restore, please refer to:-

https://docs.microsoft.com/en-us/azure/postgresql/howto-migrate-using-dump-and-restore#optimizing-th...

For complete step by step details in import and export, please refer to:-

https://docs.microsoft.com/en-us/azure/postgresql/howto-migrate-using-export-and-import

building-690854_960_720.jpg