- Home
- PostgreSQL
- Copy PostgreSQL Table Data to Another Database or Machine
Copy PostgreSQL Table Data to Another Database or Machine
By Whether migrating to another database or server, there are a couple of ways to bring along your PostgreSQL table data. In addition to pg_dump and pg_restore PostgreSQL has a powerful command called COPY thatallows us to export data in a variety of useful formats (for example, CSV with headers row). It this tutorial we focus on how to copy table data to another server.
On the first server that already has the data, we will want to get our data and COPY TO a text file:
COPY "myTableName" TO '/tmp/myTblData.dump';
Then we move the data file to the second server that needs the data, we will want to get our data and COPY TO a text file:
COPY "myTableName" FROM '/tmp/myTblData.dump';
A couple notes here:
- Make sure you first connect to the proper database before you run COPY TO or COPY FROM. Make sure the target table has already been created before running COPY FROM. If you don’t connect to the proper database or if the table does not exist you may see an error like: ERROR: relation “MyTableName” does not exist
- Make sure you clear any existing data before runnong COPY FROM
It’s that easy. Run this in psql client shell in your Linux server.