How to copy a PostgreSQL table to another database using pgAdmin

27 April,2022 by Rambler

Question: I have some tables on database on a PostgreSQL server instance. I want to copy the schema and data to another PostgreSQL  server instance . Is there a straightforward process using pgAdmin to complete this task?

Answer:  For a larger migration effort  I suggest a pg_dump\pg_restore method butA straightforward method is to use  the backup functionality on pgAdmin. 

You can also use the backup functionality in pgAdmin . These are the steps:

  • Open pgAdmin, right click the database table you want to move, select "Backup"
  • Pick the directory for the output file and suggest a filename
  • Set Format to "Plain"
  • Click the "Data/Objects" tab, check "Only data" or "only Schema" . This will depending on your objective.
  • Under the Options| Queries section, click "Use Column Inserts" and "User Insert Commands". This will generate the INSERT statements
  • Click the "Backup" button. This outputs to a .backup file
  • Open this new file using notepad. You will see the insert scripts needed for the table/data. Copy and paste these into the new database sql page in pgAdmin. Run as pgScript - Query->Execute as pgScript .     
  • If you've selected the Schema option , you may need to adjust the owner name if the owner name on the target database is different - as you may get a "role not found" error whilst applying the script on target state

Read more on PostgreSQL & migration

The truth about SQL Server to PostgreSQL migration

Spotlight on Babelfish for Aurora PostgreSQL

How to use PostgreSQL pg_basebackup


Author: Rambler (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to copy a PostgreSQL table to another database using pgAdmin


dba-ninja.com