How to copy a PostgreSQL table to another database using pgAdmin

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