Home
%3CLINGO-SUB%20id%3D%22lingo-sub-864167%22%20slang%3D%22en-US%22%3ELogin%20and%20Permission%20Tips%20and%20Tricks%20in%20using%20pg_dump%20and%20pg_restore%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-864167%22%20slang%3D%22en-US%22%3E%3CP%3EPg_dump%20and%20pg_restore%20are%20commonly%20used%20in%20data%20movement%20between%20PostgreSQL%20servers.%20Pg_dump%20and%20pg_restore%20assume%20super_user%20is%20being%20used%2C%20and%20when%20you%20are%20migrating%20data%20from%20on-premise%20to%20the%20cloud%2C%20there%20are%20some%20tips%20and%20tricks%20that%20will%20help%20you%20in%20executing%20pg_dump%20and%20pg_restore.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH3%20id%3D%22toc-hId-1625598143%22%20id%3D%22toc-hId-1625598143%22%3E1.%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Use%20--role%3Drolename%20in%20pg_dump%3C%2FH3%3E%0A%3CP%3EThere%20are%20cases%20when%20the%20login%20used%20to%20connect%20to%20PostgreSQL%20instance%20and%20the%20login%20used%20to%20run%20pg_dump%20has%20different%20privileges.%20This%20is%20very%20common%20when%20you%20move%20between%20databases.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20solution%20is%20to%20add%20%E2%80%93role%3Drolename%20switch%20in%20the%20pg_dump%20command.%20This%20switch%20will%20run%20SET%20ROLE%20rolename%20command%20after%20connecting%20to%20the%20database%2C%20in%20other%20words%2C%20this%20command%20sets%20the%20current%20SQL%20session%20to%20%3CSTRONG%3E%3CEM%3Erole_name%3C%2FEM%3E%3C%2FSTRONG%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOne%20thing%20to%20note%2C%20the%20role%20in%20--role%20should%20be%20a%20member%20of%20the%20role%20used%20in%20--username.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20example%2C%20I%E2%80%99m%20going%20to%20use%20login1%20to%20connect%20to%20the%20PostgreSQL%20host%20but%20run%20restore%20under%20login2.%20Before%20I%20run%20pg_dump%2C%20I%E2%80%99m%20going%20to%20create%20a%20role%20as%20%E2%80%98login2%E2%80%99%20if%20it%20hasn%E2%80%99t%20existed.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECREATE%20ROLE%20login2%3C%2FP%3E%0A%3CP%3EGRANT%20login2%20to%20login1%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20dump%3A%3C%2FP%3E%0A%3CP%3Epg_dump%20-v%20--no-owner%20--host%3Dlocalhost%20--port%3D5432%20--username%3Dlogin1%20--role%3Dlogin2%20--dbname%3Dmovies%20D%3A%5Cmovies.dump%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH3%20id%3D%22toc-hId--926558818%22%20id%3D%22toc-hId--926558818%22%3E2.%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Use%20--role%3Drolename%20in%20pg_restore%3C%2FH3%3E%0A%3CP%3ESimilarly%2C%20you%20can%20also%20use%20--role%3Drolename%20in%20pg_restore%20when%20login%20used%20to%20connect%20to%20PostgreSQL%20instance%20and%20the%20login%20used%20to%20run%20pg_restore%20has%20different%20privileges.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20switch%20will%20run%20SET%20ROLE%20%3CEM%3Erolename%3C%2FEM%3E%20command%20after%20connecting%20to%20the%20database%2C%20in%20other%20words%2C%20this%20command%20sets%20the%20current%20SQL%20session%20to%20%3CSTRONG%3E%3CEM%3Erole_name%3C%2FEM%3E%3C%2FSTRONG%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOne%20thing%20to%20note%2C%20the%20role%20in%20--role%20should%20be%20a%20member%20of%20the%20role%20used%20in%20--username.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20example%2C%20I%E2%80%99m%20going%20to%20use%20login1%20to%20connect%20to%20the%20PostgreSQL%20host%20but%20run%20restore%20under%20login2.%20Before%20I%20run%20pg_restore%2C%20I%E2%80%99m%20going%20to%20create%20a%20role%20as%20%E2%80%98login2%E2%80%99%20if%20it%20hasn%E2%80%99t%20existed.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECREATE%20ROLE%20login2%3C%2FP%3E%0A%3CP%3EGRANT%20login2%20to%20login1%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20restore%3A%3C%2FP%3E%0A%3CP%3Epg_restore%20-v%20--no-owner%20--host%3D%20mydemoserver.postgres.database.azure.com%20--port%3D5432%20--username%3Dlogin1%40mydemoserver%20--role%3Dlogin2%20--dbname%3Dmovies%20D%3A%5Cmovies.dump%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20assign%20database%20owner%20to%20login2%2C%20simply%20run%20alter%20database%3A-%3C%2FP%3E%0A%3CP%3EALTER%20DATABASE%20movies%20OWNER%20TO%20login2%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH3%20id%3D%22toc-hId-816251517%22%20id%3D%22toc-hId-816251517%22%3E3.%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Use%20--use-set-session-authorization%20in%20pg_dump%3C%2FH3%3E%0A%3CP%3EIn%20some%20use%20cases%2C%20you%20would%20want%20the%20application%20or%20the%20user%20to%20authenticate%20through%20web%20app%20through%20JDBC%20and%20access%20the%20database.%20If%20there%20is%20a%20scenario%20to%20issue%20a%20dump%20through%20authenticated%20user%20within%20the%20webapp%2C%20you%20can%20use%20SET%20SESSION%20AUTHORIZATION%20to%20proxy%20the%20user%20on%20a%20JDBC%20session.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E--use-set-session-authorization%20issues%20SET%20SESSION%20AUTHORIZATION%20commands%20instead%20of%20ALTER%20ONWER%20commands%20to%20determine%20object%20ownership.%20Please%20note%2C%20a%20dump%20using%20SET%20SESSION%20AUTHORIZATION%20will%20require%20superuser%20privileges%20to%20restore%20correctly.%3C%2FP%3E%0A%3CP%3ESee%20more%20on%20SET%20SESSION%20AUTHORIZATION%20%3CA%20href%3D%22https%3A%2F%2Fwww.postgresql.org%2Fdocs%2Fcurrent%2Fsql-set-session-authorization.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH3%20id%3D%22toc-hId--1735905444%22%20id%3D%22toc-hId--1735905444%22%3E4.%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Use%20--no-owner%20in%20pg_restore%3C%2FH3%3E%0A%3CP%3EBy%20default%2C%20pg_restore%20issues%20ALTER%20OWNER%20or%20SET%20SESSION%20AUTHORIZATION%20statements%20to%20set%20ownership%20of%20created%20schema%20elements.%20Using%20--no-owner%20in%20pg_restore%2C%20the%20username%20you%20specified%20in%20--username%20will%20own%20all%20the%20created%20objects%20which%20is%20helpful%20when%20you%20don%E2%80%99t%20have%20super_user%20login%20permission.%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EFor%20example%3A-%3C%2FP%3E%0A%3CP%3Epg_restore%20-v%20--no-owner%20--host%3Dmydemoserver.postgres.database.azure.com%20--port%3D5432%20--username%3Dmylogin%40mydemoserver%20--dbname%3Dmypgsqldb%20testdb.dump%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20complete%20step%20by%20step%20details%20in%20running%20dump%20and%20restore%2C%20please%20refer%20to%3A%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fpostgresql%2Fhowto-migrate-using-dump-and-restore%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fpostgresql%2Fhowto-migrate-using-dump-and-restore%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EFor%20more%20information%20on%20performance%20optimization%20in%20doing%20pg_dump%20and%20pg_restore%2C%20please%20refer%20to%3A-%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fpostgresql%2Fhowto-migrate-using-dump-and-restore%23optimizing-the-migration-process%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fpostgresql%2Fhowto-migrate-using-dump-and-restore%23optimizing-the-migration-process%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EFor%20complete%20step%20by%20step%20details%20in%20import%20and%20export%2C%20please%20refer%20to%3A-%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fpostgresql%2Fhowto-migrate-using-export-and-import%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fpostgresql%2Fhowto-migrate-using-export-and-import%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-864167%22%20slang%3D%22en-US%22%3E%3CP%3EH%3CSPAN%20style%3D%22display%3A%20inline%20!important%3B%20float%3A%20none%3B%20background-color%3A%20%23ffffff%3B%20color%3A%20%23333333%3B%20cursor%3A%20text%3B%20font-family%3A%20inherit%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.7142%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%3Eere%20are%20some%20tips%20and%20tricks%20that%20will%20help%20you%20in%20executing%20pg_dump%20and%20pg_restore%20in%20cloud%20environments.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-864167%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Edump%20and%20restore%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Epg_dump%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Epg_restore%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Epostgres%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Epostgresql%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

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