Backup & Restore using PostgreSQL COPY
Table of Contents
1. Understanding the COPY Command
PostgreSQL’s COPY command is one of the most efficient ways to export and import data to/from files. Unlike traditional INSERT statements, COPY can transfer large amounts of data in seconds, making it ideal for backups, data migrations, and bulk operations. Remember to always test restore procedures in non-production environments and maintain multiple backups in different formats for maximum data safety.
- COPY TO: Exports data from a PostgreSQL table to a file
- COPY FROM: Imports data from a file into a PostgreSQL table
- Why use it: 10-100x faster than individual INSERT statements for bulk operations
- Flexible formatting: Support for tab-delimited, comma-delimited, CSV, and custom formats
- Query support: Can export results of complex queries, not just entire tables
- Selective import: Import only specific columns; others receive NULL or DEFAULT values
Key Point: COPY is not meant to replace full backups using pg_dump, but it’s perfect for selective data export/import, data migrations, and quick backups of specific tables.
2. Setup: Create Schema & Test Data
First, create a schema and sample table with test data:
postgres=# \c
You are now connected to database "postgres" as user "postgres".
-- Create user
postgres=# create user edbadm password 'edbadm';
CREATE ROLE
postgres=#
-- Create Database
postgres=# CREATE DATABASE edbua;
CREATE DATABASE
postgres=#
-- Create schema and insert some data.
postgres=# \c edbua
You are now connected to database "edbua" as user "postgres".
edbua=#
edbua=# CREATE SCHEMA edb AUTHORIZATION edbadm;
CREATE SCHEMA
edbua=#
postgres=# \c edbua edbadm
You are now connected to database "edbua" as user "edbadm".
edbua=>
edbua=> CREATE TABLE edb.emp (id SERIAL PRIMARY KEY, name TEXT NOT NULL, designation TEXT, project TEXT,company TEXT);
CREATE TABLE
edbua=> INSERT INTO edb.emp (name, designation, project, company) VALUES ('Sugi', 'DBA', 'Jetstar', 'iGATE');
INSERT 0 1
edbua=> INSERT INTO edb.emp (name, designation, project, company) VALUES ('Teja', 'DBA', 'RCM', 'iGATE');
INSERT 0 1
edbua=> INSERT INTO edb.emp (name, designation, project, company) VALUES ('RAJ', 'DBA', 'RCM', 'iGATE');
INSERT 0 1
edbua=> INSERT INTO edb.emp (name, designation, project, company) VALUES ('Arun', 'Developer', 'Jetstar', 'TCS');
INSERT 0 1
edbua=> INSERT INTO edb.emp (name, designation, project, company) VALUES ('Priya', 'QA', 'RCM', 'Wipro');
INSERT 0 1
edbua=> SELECT * FROM edb.emp;
id | name | designation | project | company
----+-------+-------------+---------+---------
1 | Sugi | DBA | Jetstar | iGATE
2 | Teja | DBA | RCM | iGATE
3 | RAJ | DBA | RCM | iGATE
4 | Arun | Developer | Jetstar | TCS
5 | Priya | QA | RCM | Wipro
(5 rows)
edbua=> SELECT COUNT(*) FROM edb.emp;
count
-------
5 <-----
(1 row)
edbua=>
Backup directory setup (execute from terminal as root):
[root@pgdb02 ~]# mkdir -p /pgBackup/edbua/backup/
[root@pgdb02 ~]# chown postgres:postgres /pgBackup/edbua/backup/
[root@pgdb02 ~]#3. Basic Export & Import (Tab-Delimited)
Export: Export all data from a table to a file using the default tab-delimited format:
-- Basic COPY: Exports all data (tab-delimited by default)
postgres=# \c edbua
You are now connected to database "edbua" as user "postgres".
edbua=#
edbua=# COPY edb.emp TO '/pgBackup/edbua/backup/table_backup_edbua_edb_emp.txt';
COPY 5
edbua=#This creates a simple text file with each record on one line, columns separated by tabs. To view the exported file from terminal:
[postgres@pgdb02 ~]$ ls -ltr /pgBackup/edbua/backup/table_backup_edbua_edb_emp.txt
-rw-r--r--. 1 postgres postgres 116 Jul 10 15:17 /pgBackup/edbua/backup/table_backup_edbua_edb_emp.txt
[postgres@pgdb02 ~]$
[postgres@pgdb02 ~]$ wc -l /pgBackup/edbua/backup/table_backup_edbua_edb_emp.txt
5 /pgBackup/edbua/backup/table_backup_edbua_edb_emp.txt
[postgres@pgdb02 ~]$
[postgres@pgdb02 ~]$ cat /pgBackup/edbua/backup/table_backup_edbua_edb_emp.txt
1 Sugi DBA Jetstar iGATE
2 Teja DBA RCM iGATE
3 RAJ DBA RCM iGATE
4 Arun Developer Jetstar TCS
5 Priya QA RCM Wipro
[postgres@pgdb02 ~]$
The output shows each employee record with columns separated by tabs. This format is useful for simple, fast backups.
Import: Restore data from the backup file:
--- Simulate data loss - truncate the table edbua=# TRUNCATE TABLE edb.emp CASCADE; TRUNCATE TABLE edbua=# SELECT COUNT(*) FROM edb.emp; count ------- 0 (1 row) edbua=# COPY edb.emp FROM '/pgBackup/edbua/backup/table_backup_edbua_edb_emp.txt'; COPY 5 edbua=# SELECT COUNT(*) FROM edb.emp; count ------- 5 <---- (1 row) edbua=# SELECT * FROM edb.emp; id | name | designation | project | company ----+-------+-------------+---------+--------- 1 | Sugi | DBA | Jetstar | iGATE 2 | Teja | DBA | RCM | iGATE 3 | RAJ | DBA | RCM | iGATE 4 | Arun | Developer | Jetstar | TCS 5 | Priya | QA | RCM | Wipro (5 rows) edbua=#
4. Custom Delimiters – Export & Import
Use different delimiters to format data for different systems and tools. Choose the delimiter that best matches your target system or data import tool. When importing, ensure the DELIMITER option matches the file format.
Export with Pipe Delimiter (|):
--- Export edbua=# \c You are now connected to database "edbua" as user "postgres". edbua=# COPY edb.emp TO '/pgBackup/edbua/backup/table_backup_edbua_edb_emp_pipe.txt' WITH DELIMITER '|'; COPY 5 edbua=# edbua=# \! cat /pgBackup/edbua/backup/table_backup_edbua_edb_emp_pipe.txt 1|Sugi|DBA|Jetstar|iGATE 2|Teja|DBA|RCM|iGATE 3|RAJ|DBA|RCM|iGATE 4|Arun|Developer|Jetstar|TCS 5|Priya|QA|RCM|Wipro edbua=# ----- IMPORT --- Simulate data loss - truncate the table edbua=# TRUNCATE TABLE edb.emp CASCADE; TRUNCATE TABLE edbua=# SELECT COUNT(*) FROM edb.emp; count ------- 0 (1 row) edbua=# COPY edb.emp FROM '/pgBackup/edbua/backup/table_backup_edbua_edb_emp_pipe.txt' WITH DELIMITER '|'; COPY 5 edbua=# SELECT COUNT(*) FROM edb.emp; count ------- 5 <---- (1 row) edbua=# SELECT * FROM edb.emp; id | name | designation | project | company ----+-------+-------------+---------+--------- 1 | Sugi | DBA | Jetstar | iGATE 2 | Teja | DBA | RCM | iGATE 3 | RAJ | DBA | RCM | iGATE 4 | Arun | Developer | Jetstar | TCS 5 | Priya | QA | RCM | Wipro (5 rows) edbua=#
Export with Comma Delimiter:
-- EXPORT edbua=# \c You are now connected to database "edbua" as user "postgres". edbua=# edbua=# COPY edb.emp TO '/pgBackup/edbua/backup/table_backup_edbua_edb_emp_comma.txt' WITH DELIMITER ','; COPY 5 edbua=# edbua=# \! cat /pgBackup/edbua/backup/table_backup_edbua_edb_emp_comma.txt 1,Sugi,DBA,Jetstar,iGATE 2,Teja,DBA,RCM,iGATE 3,RAJ,DBA,RCM,iGATE 4,Arun,Developer,Jetstar,TCS 5,Priya,QA,RCM,Wipro edbua=# -- IMPORT --- Simulate data loss - truncate the table edbua=# TRUNCATE TABLE edb.emp CASCADE; TRUNCATE TABLE edbua=# SELECT COUNT(*) FROM edb.emp; count ------- 0 (1 row) edbua=# COPY edb.emp FROM '/pgBackup/edbua/backup/table_backup_edbua_edb_emp_comma.txt' WITH DELIMITER ','; COPY 5 edbua=# SELECT COUNT(*) FROM edb.emp; count ------- 5 <------- (1 row) edbua=# SELECT * FROM edb.emp; id | name | designation | project | company ----+-------+-------------+---------+--------- 1 | Sugi | DBA | Jetstar | iGATE 2 | Teja | DBA | RCM | iGATE 3 | RAJ | DBA | RCM | iGATE 4 | Arun | Developer | Jetstar | TCS 5 | Priya | QA | RCM | Wipro (5 rows) edbua=#
Export with Semicolon Delimiter:
-- EXPORT
edbua=# COPY edb.emp TO '/pgBackup/edbua/backup/table_backup_edbua_edb_emp_semicolon.txt' WITH DELIMITER ';';
COPY 5
edbua=# \! cat /pgBackup/edbua/backup/table_backup_edbua_edb_emp_semicolon.txt
1;Sugi;DBA;Jetstar;iGATE
2;Teja;DBA;RCM;iGATE
3;RAJ;DBA;RCM;iGATE
4;Arun;Developer;Jetstar;TCS
5;Priya;QA;RCM;Wipro
edbua=#
-- IMPORT
--- Simulate data loss - truncate the table
edbua=# TRUNCATE TABLE edb.emp CASCADE;
TRUNCATE TABLE
edbua=# SELECT COUNT(*) FROM edb.emp;
count
-------
0
(1 row)
edbua=# COPY edb.emp FROM '/pgBackup/edbua/backup/table_backup_edbua_edb_emp_semicolon.txt' WITH DELIMITER ';';
COPY 5
edbua=# SELECT COUNT(*) FROM edb.emp;
count
-------
5 <-----------------
(1 row)
edbua=# SELECT * FROM edb.emp;
id | name | designation | project | company
----+-------+-------------+---------+---------
1 | Sugi | DBA | Jetstar | iGATE
2 | Teja | DBA | RCM | iGATE
3 | RAJ | DBA | RCM | iGATE
4 | Arun | Developer | Jetstar | TCS
5 | Priya | QA | RCM | Wipro
(5 rows)
edbua=#
5. CSV Format – Export & Import with Headers
CSV (Comma-Separated Values) format is the most portable and human-readable format. Include headers for better clarity.
Export as CSV with column headers & Import
-- EXPORT edbua=# COPY edb.emp TO '/pgBackup/edbua/backup/table_backup_edbua_edb_emp.csv' WITH CSV HEADER; COPY 5 edbua=# \! cat /pgBackup/edbua/backup/table_backup_edbua_edb_emp.csv id,name,designation,project,company 1,Sugi,DBA,Jetstar,iGATE 2,Teja,DBA,RCM,iGATE 3,RAJ,DBA,RCM,iGATE 4,Arun,Developer,Jetstar,TCS 5,Priya,QA,RCM,Wipro edbua=# edbua=# \! ls -l /pgBackup/edbua/backup/table_backup_edbua_edb_emp.csv -rw-r--r--. 1 postgres postgres 152 Jul 10 15:41 /pgBackup/edbua/backup/table_backup_edbua_edb_emp.csv edbua=# ---- IMPORT --- Simulate data loss - truncate the table edbua=# TRUNCATE TABLE edb.emp CASCADE; TRUNCATE TABLE edbua=# SELECT COUNT(*) FROM edb.emp; count ------- 0 (1 row) edbua=# COPY edb.emp FROM '/pgBackup/edbua/backup/table_backup_edbua_edb_emp.csv' WITH CSV HEADER; COPY 5 edbua=# SELECT COUNT(*) FROM edb.emp; count ------- 5 <------ (1 row) edbua=# SELECT * FROM edb.emp; id | name | designation | project | company ----+-------+-------------+---------+--------- 1 | Sugi | DBA | Jetstar | iGATE 2 | Teja | DBA | RCM | iGATE 3 | RAJ | DBA | RCM | iGATE 4 | Arun | Developer | Jetstar | TCS 5 | Priya | QA | RCM | Wipro (5 rows) edbua=#
Export as CSV with custom delimiter and header & Import
edbua=# COPY edb.emp TO '/pgBackup/edbua/backup/table_backup_edbua_edb_emp_custom.csv' WITH CSV HEADER DELIMITER '|';
COPY 5
edbua=# \! cat /pgBackup/edbua/backup/table_backup_edbua_edb_emp_custom.csv
id|name|designation|project|company
1|Sugi|DBA|Jetstar|iGATE
2|Teja|DBA|RCM|iGATE
3|RAJ|DBA|RCM|iGATE
4|Arun|Developer|Jetstar|TCS
5|Priya|QA|RCM|Wipro
edbua=#
--- IMPORT
--- Simulate data loss - truncate the table
edbua=# TRUNCATE TABLE edb.emp CASCADE;
TRUNCATE TABLE
edbua=# SELECT COUNT(*) FROM edb.emp;
count
-------
0
(1 row)
edbua=# COPY edb.emp FROM '/pgBackup/edbua/backup/table_backup_edbua_edb_emp_custom.csv' WITH CSV HEADER DELIMITER '|';
COPY 5
edbua=# SELECT COUNT(*) FROM edb.emp;
count
-------
5 <-------------------
(1 row)
edbua=# SELECT * FROM edb.emp;
id | name | designation | project | company
----+-------+-------------+---------+---------
1 | Sugi | DBA | Jetstar | iGATE
2 | Teja | DBA | RCM | iGATE
3 | RAJ | DBA | RCM | iGATE
4 | Arun | Developer | Jetstar | TCS
5 | Priya | QA | RCM | Wipro
(5 rows)
edbua=#
We will see the first line contains column names: id,name,designation,project,company, followed by the data. This format is ideal for importing into Excel, Google Sheets, or other database systems.
6. Filtered Data – Export & Import Specific Queries
Export and import only specific data based on conditions using queries.
Export only DBAs from iGATE:
edbua=# SELECT id, name, designation, project, company FROM edb.emp WHERE designation = 'DBA' AND company = 'iGATE';
id | name | designation | project | company
----+------+-------------+---------+---------
1 | Sugi | DBA | Jetstar | iGATE
2 | Teja | DBA | RCM | iGATE
3 | RAJ | DBA | RCM | iGATE
(3 rows)
edbua=# COPY ( SELECT id, name, designation, project, company FROM edb.emp WHERE designation = 'DBA' AND company = 'iGATE' ) TO '/pgBackup/edbua/backup/edb_emp_dbas.txt' WITH DELIMITER '|';
COPY 3
edbua=#
edbua=# \! cat /pgBackup/edbua/backup/edb_emp_dbas.txt
1|Sugi|DBA|Jetstar|iGATE
2|Teja|DBA|RCM|iGATE
3|RAJ|DBA|RCM|iGATE
edbua=#
Export specific columns only:
edbua=# SELECT name, designation, company FROM edb.emp WHERE company IN ('iGATE', 'TCS');
name | designation | company
------+-------------+---------
Sugi | DBA | iGATE
Teja | DBA | iGATE
RAJ | DBA | iGATE
Arun | Developer | TCS
(4 rows)
edbua=# COPY ( SELECT name, designation, company FROM edb.emp WHERE company IN ('iGATE', 'TCS') ) TO '/pgBackup/edbua/backup/edb_emp_selected.txt' WITH CSV HEADER;
COPY 4
edbua=#
edbua=# \! cat /pgBackup/edbua/backup/edb_emp_selected.txt
name,designation,company
Sugi,DBA,iGATE
Teja,DBA,iGATE
RAJ,DBA,iGATE
Arun,Developer,TCS
edbua=#
---- Import with column mapping:
-- Create a staging table with different column structure and Import specific columns mapped to different table
edbua=# CREATE TABLE edb.emp_staging ( full_name TEXT, job_title TEXT, employer TEXT );
CREATE TABLE
edbua=# COPY edb.emp_staging (full_name, job_title, employer) FROM '/pgBackup/edbua/backup/edb_emp_selected.txt' WITH CSV HEADER;
COPY 4
edbua=# SELECT * FROM edb.emp_staging;
full_name | job_title | employer
-----------+-----------+----------
Sugi | DBA | iGATE
Teja | DBA | iGATE
RAJ | DBA | iGATE
Arun | Developer | TCS
(4 rows)
edbua=#
Export with row numbering:
edbua=# SELECT ROW_NUMBER() OVER () as row_num, name, designation, project FROM edb.emp ORDER BY name;
row_num | name | designation | project
---------+-------+-------------+---------
4 | Arun | Developer | Jetstar
5 | Priya | QA | RCM
3 | RAJ | DBA | RCM
1 | Sugi | DBA | Jetstar
2 | Teja | DBA | RCM
(5 rows)
edbua=# COPY ( SELECT ROW_NUMBER() OVER () as row_num, name, designation, project FROM edb.emp ORDER BY name ) TO '/pgBackup/edbua/backup/edb_emp_numbered.csv' WITH CSV HEADER;
COPY 5
edbua=#
edbua=# \! cat /pgBackup/edbua/backup/edb_emp_numbered.csv
row_num,name,designation,project
4,Arun,Developer,Jetstar
5,Priya,QA,RCM
3,RAJ,DBA,RCM
1,Sugi,DBA,Jetstar
2,Teja,DBA,RCM
edbua=#
This approach is perfect for creating targeted backups, archiving old data, generating reports, and selectively importing data into different table structures.
7. Handling Special Cases (NULLs, Special Characters, Duplicates)
A. Handling NULL Values During Export & Import:
-- Create table with nullable columns
edbua=# CREATE TABLE edb.emp_nullable ( id SERIAL PRIMARY KEY, name TEXT, designation TEXT, project TEXT, company TEXT );
CREATE TABLE
edbua=#
-- Insert data with some NULL values
edbua=# INSERT INTO edb.emp_nullable (name, designation, project, company) VALUES ('Sugi', 'DBA', 'Jetstar', NULL), ('Teja', 'DBA', NULL, 'iGATE'), ('RAJ', NULL, 'RCM', 'iGATE');
INSERT 0 3
edbua=#
-- Export (NULLs appear as empty fields)
edbua=# COPY edb.emp_nullable TO '/pgBackup/edbua/backup/edb_emp_nulls.csv' WITH CSV HEADER;
COPY 3
edbua=#
edbua=# SELECT * FROM edb.emp_nullable;
id | name | designation | project | company
----+------+-------------+---------+---------
1 | Sugi | DBA | Jetstar |
2 | Teja | DBA | | iGATE
3 | RAJ | | RCM | iGATE
(3 rows)
edbua=#
-- Import back (empty fields become NULL)
edbua=# TRUNCATE TABLE edb.emp_nullable;
TRUNCATE TABLE
edbua=# COPY edb.emp_nullable FROM '/pgBackup/edbua/backup/edb_emp_nulls.csv' WITH CSV HEADER;
COPY 3
edbua=# SELECT * FROM edb.emp_nullable; -- Notice NULL values preserved
id | name | designation | project | company
----+------+-------------+---------+---------
1 | Sugi | DBA | Jetstar |
2 | Teja | DBA | | iGATE
3 | RAJ | | RCM | iGATE
(3 rows)
edbua=#
B. Exporting & Importing Data with Special Characters:
-- Create table with special characters
edbua=# CREATE TABLE edb.emp_special ( id SERIAL PRIMARY KEY, name TEXT, description TEXT );
CREATE TABLE
edbua=#
-- Insert data with quotes and commas
edbua=# INSERT INTO edb.emp_special (name, description) VALUES ('John "The Expert" Doe', 'Senior DBA, 10 years experience'), ('Mary O''Brien', 'Works with Oracle, PostgreSQL, MySQL');
INSERT 0 2
edbua=#
-- Export with proper escaping (CSV handles this automatically)
edbua=# COPY edb.emp_special TO '/pgBackup/edbua/backup/edb_emp_special.csv' WITH CSV HEADER QUOTE '"' ESCAPE '"';
COPY 2
edbua=#
edbua=# SELECT * FROM edb.emp_special;
id | name | description
----+-----------------------+--------------------------------------
1 | John "The Expert" Doe | Senior DBA, 10 years experience
2 | Mary O'Brien | Works with Oracle, PostgreSQL, MySQL
(2 rows)
edbua=#
-- Import from the file (escaping automatically handled)
edbua=# TRUNCATE TABLE edb.emp_special;
TRUNCATE TABLE
edbua=# COPY edb.emp_special FROM '/pgBackup/edbua/backup/edb_emp_special.csv' WITH CSV HEADER QUOTE '"' ESCAPE '"';
COPY 2
edbua=# SELECT * FROM edb.emp_special;
id | name | description
----+-----------------------+--------------------------------------
1 | John "The Expert" Doe | Senior DBA, 10 years experience
2 | Mary O'Brien | Works with Oracle, PostgreSQL, MySQL
(2 rows)
edbua=#
C. Handling Duplicate Keys:
-- If importing data might have duplicate primary keys:
-- Option 1: Truncate first (removes all data)
TRUNCATE TABLE edb.emp CASCADE;
-- Option 2: Import to staging table first, then validate
CREATE TABLE edb.emp_staging (LIKE edb.emp);
COPY edb.emp_staging FROM '/pgBackup/edbua/backup/edb_emp.csv' WITH CSV HEADER;
-- Check for issues, then copy valid data
SELECT COUNT(*) FROM edb.emp_staging;
INSERT INTO edb.emp SELECT * FROM edb.emp_staging WHERE id NOT IN (SELECT id FROM edb.emp);8. Copying Data Between Tables (emp to emp02)
Transfer data from one table to another table.
-- Step 1: Export emp data to file
edbua=# select * from edb.emp;
id | name | designation | project | company
----+-------+-------------+---------+---------
1 | Sugi | DBA | Jetstar | iGATE
2 | Teja | DBA | RCM | iGATE
3 | RAJ | DBA | RCM | iGATE
4 | Arun | Developer | Jetstar | TCS
5 | Priya | QA | RCM | Wipro
(5 rows)
edbua=# COPY edb.emp TO '/pgBackup/edbua/backup/emp_to_emp02.csv' WITH CSV HEADER;
COPY 5
edbua=#
-- Step 2: Create emp02 table
edbua=# CREATE TABLE edb.emp02 ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, designation TEXT, project TEXT, company TEXT );
CREATE TABLE
edbua=#
-- Step 3: Import data from file into emp02
edbua=# select * from edb.emp02;
id | name | designation | project | company
----+------+-------------+---------+---------
(0 rows)
edbua=# COPY edb.emp02 FROM '/pgBackup/edbua/backup/emp_to_emp02.csv' WITH CSV HEADER;
COPY 5
edbua=# select * from edb.emp02;
id | name | designation | project | company
----+-------+-------------+---------+---------
1 | Sugi | DBA | Jetstar | iGATE
2 | Teja | DBA | RCM | iGATE
3 | RAJ | DBA | RCM | iGATE
4 | Arun | Developer | Jetstar | TCS
5 | Priya | QA | RCM | Wipro
(5 rows)
edbua=#Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you
Rajasekhar Amudala