PostgreSQL move data

Table of Contents Backup Old-school : backup PG files One schema One cluster import Move data chunks \COPY Syntax A first try A better way

Backup

Old-school : backup PG files

(Here, data does NOT limit to table data, but also indexes, roles, code and include all cluster's database) Steps:

• in pg_cli, get directory SHOW data_directory;
• stop pg sudo systemctl stop postgresql
• check it's stopped sudo systemctl status postgresql , should give (..) Stopped PostgreSQL RDBMS.
• backup the directory
• start pg sudo systemctl start postgresql
• check it's started sudo systemctl status postgresql , should give (..) Started PostgreSQL RDBMS.

One schema

Key points:

• on a single database
• exports : all instance data (table data + DDL (table structure - index definition) )
• does NOT exports: database container, user, role, index data
• produce SQL queries, so fully compatible between version/architectures..

Steps:

• export pg_dump -h HOST_NAME -p PORT_NUMBER -U USER_NAME DATABASE_NAME > BACKUP_FILE_NAME
• remove database
• create database
• import psql -h HOST_NAME -p PORT_NUMBER DATABASE_NAME < BACKUP_FILE_NAME
• collect statistics

One cluster

On each and every database of the cluster
Exports everything
Steps:

• export pg_dumpall -h HOST_NAME -p PORT_NUMBER -U USER_NAME > BACKUP_FILE_NAME
• remove cluster
• import psql -f BACKUP_FILE_NAME postgres

import

pg_restore

Dump content is encrypted, so to search

• objects name only : pg_restore -l <FILE-NAME>
• search instruction : pg_restore <FILE-NAME>-f - | grep <INSTRUCTION>
• extract all : pg_restore <ENCRYPTED_DUMP_FILENAME>.pgsql -f - > <HUMAN_READBALE_DUMP_FILENAME>.sql

Import specified: pg_restore <DUMP-FILE-NAME> --use-list <OBJECT-LIST-FILE-NAME>

Move data chunks

Overview

Solutions:

• shell access to remote server: COPY
• NO shell access to remote server (access only through psql ) : \COPY

\COPY

Syntax

Use temporary file

// Connect remotely to source database using psql
\COPY users TO 'users.csv' CSV HEADER

// Connect remotely to target database using psql
\COPY users FROM 'users.csv' WITH CSV HEADER;

Use temporary file and a unix stream to filter data to be imported ?

psql
    -h localhost \
    -d your_primary_db \
    -U postgres -c \
    "\copy users (id, email, first_name, last_name) from STDIN
    with delimiter as ','" \ < /tmp/users.csv

From a remote database to another remote database

// SOURCE
psql \
    -U user_name \
    -h production_server \
    -d database_name \
    -c "\\copy users to stdout" | \
// TARGET
psql -U user_name \
    -h staging_server \
    -d database_name \
    -c "\\copy users from stdin"

Ie on localhost database, from source to target schema, on foo table

A first try

// Create dataset
CREATE DATABASE source; 
CONNECT source;
CREATE TABLE foo (ID INTEGER PRIMARY KEY);
INSERT INTO foo VALUES (1);
INSERT INTO foo VALUES (2);

CREATE DATABASE target; 
CONNECT target;
CREATE TABLE foo (ID INTEGER PRIMARY KEY);  
INSERT INTO foo VALUES (0);
INSERT INTO foo VALUES (1);

// Copy
psql \
    -U postgres \
    -h localhost \
    -d source \
    -c "\\copy foo to stdout" | \
psql -U postgres \
    -h localhost \
    -d target \
    -c "\\copy foo from stdin"

Note: \COPY does NOT:

• create table structure
• disable constraints (especially PK)
• ROLLBACK on error

So, in the example below, you get the error message and no row imported

ERROR:  duplicate key value violates unique constraint "foo_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY foo, line 1

You need to delete the offending record to get it working: DELETE FROM foo WHERE id=1;

Then try again

// launch copy, get 
COPY 2

// Check imported data
CONNECT target
SELECT * FROM foo;
|------|
| id   |
|------|
| 0    |
| 1    |
| 2    |
+------+

A better way

Ramp up to avoid this ! Use SQL query

CONNECT source;
CREATE TABLE foo (ID INTEGER PRIMARY KEY);
INSERT INTO foo VALUES (1);


psql \
    -U postgres \
    -h localhost \
    -d source \
    -c "\\copy (SELECT * FROM foo WHERE id>=3) to stdout" | \
psql -U postgres \
    -h localhost \
    -d target \
    -c "\\copy foo from stdin"
COPY 1

// Check imported data
\CONNECT target
SELECT * FROM foo;
+------+
| id   |
|------|
| 0    |
| 1    |
| 2    |
| 3    |
+------+