Transfer data between databases with PostgreSQL

sql

database-migration

postgresql

data-transfer

byAnton Shumikhin·Dec 20, 2024

Get your PostgreSQL databases to chat using COPY for local file exchange or FDW for near real-time data communication:

-- Dump data to CSV (you're ripping the soul out of your data):
COPY your_table TO '/tmp/data.csv' WITH CSV;

-- Load CSV data, like giving your data a new home:
COPY your_table FROM '/tmp/data.csv' WITH CSV;

Or set up an FDW hot line between databases:

-- Setting up your own chat room for databases
CREATE EXTENSION postgres_fdw;
CREATE SERVER src_srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'source_host', dbname 'source_db');
CREATE USER MAPPING FOR CURRENT_USER SERVER src_srv OPTIONS (user 'source_user', passwd 'source_pass');
CREATE FOREIGN TABLE foreign_your_table (LIKE your_table) SERVER src_srv OPTIONS (table_name 'your_table');

-- Hey source table, do you copy? Over
INSERT INTO your_table SELECT * FROM foreign_your_table;

Choose COPY for quick local data movements or the FDW for active database connections.

The golden duo: pg_dump & psql

For inter-database migrations, you can count on pg_dump and psql. pg_dump helps you pack all your data, while psql makes sure it reaches its new home:

-- pg_dump: let's move out
-- psql: receiving data with open arms
pg_dump -U user_name -t table_name source_db | psql -U user_name target_db

Let's not forget about permissions. You may need to GRANT them in the new database:

-- You shall pass!
GRANT SELECT, INSERT, UPDATE, DELETE ON table_name TO target_user;

Sometimes, you need to reassign the ownership of the transferred table:

-- Because every table needs a true owner 
ALTER TABLE table_name OWNER TO new_owner;

Matching primary keys and column types is crucial. You won't put a square peg in a round hole, will you?

SQL-based data teleportation

For one-time transfers or some sly data movement across databases without permanent aftereffects, the classic INSERT INTO ... SELECT is your SQL magic spell:

-- Abracadabra! Teleport my data!
INSERT INTO new_db.table_name
SELECT * FROM old_db.table_name;

Advanced consistent transfers: FDW and dblink

Constant data flows require better tools. Foreign Data Wrappers (FDW) and dblink make your databases chat like old buddies:

-- DBlink, the chatterbox you need:
CREATE EXTENSION dblink;
SELECT dblink_connect('connection_name', 'hostaddr=127.0.0.1 port=5432 dbname=target_db user=target_user password=target_pass');
SELECT * FROM dblink('connection_name', ':)') AS data(column1 datatype1, column2 datatype2, ...);

-- For heavy traffic, use a temporary road (table):
BEGIN;
CREATE TEMP TABLE tmp_table AS
SELECT * FROM dblink('connection_name', 'SELECT * FROM foreign_table') AS data(column1 datatype1, column2 datatype2, ...);

-- Once done, reopen the main highway (Drop the temporary table):
DROP TABLE tmp_table;
COMMIT;

Wait, are the primary keys matching? Are column types compatible? Never forget these checks!

Avoid pitfalls, plan for the unknown

Spot and fix permission issues in your destination database. No VIP pass? No entry.
Ownership changes may make you feel like a villain, but they're needed. Be a good villain.
Check your network settings. No one likes bad connections, especially databases.