Copy

Let's create a database and a table in it.

α=> CREATE DATABASE backup_overview;
CREATE DATABASE
α=> \c backup_overview
You are now connected to database "backup_overview" as user "student".
α=> CREATE TABLE t(id numeric, s text);
CREATE TABLE
α=> INSERT INTO t VALUES (1, 'Hello!'), (2, ''), (3, NULL);
INSERT 0 3

α=> SELECT * FROM t;
 id |   s    
----+--------
  1 | Hello!
  2 | 
  3 | 
(3 rows)


Here is what the COPY command shows us:

α=> COPY t TO stdout;
1	Hello!
2	
3	\N

Note that an empty string and NULL are different, although SELECT by default doesn't show the difference.


We can also input values with the same command:

α=> TRUNCATE TABLE t;
TRUNCATE TABLE
α=> COPY t FROM STDIN;
1	Hi there!
2	
3	\N
\.
COPY 3

Let's check:

α=> SELECT * FROM t;
 id |     s     
----+-----------
  1 | Hi there!
  2 | 
  3 | 
(3 rows)

α=> COPY t TO stdout;
1	Hi there!
2	
3	\N

pg_dump

Let's have a look at the plain format of the pg_dump utility. Note that the table rows are dumped as a COPY command.

Any changes made to the template1 database will also get into the backup. That's why the database to which you are going to restore the objects from the dump, should be created from the template0. Here we specified the --create parameter which adds all necessary commands, so we don't have to worry about it.

postgres$ pg_dump -d backup_overview --create
--
-- PostgreSQL database dump
--

-- Dumped from database version 11.1 (Ubuntu 11.1-1.pgdg16.04+1)
-- Dumped by pg_dump version 11.1 (Ubuntu 11.1-1.pgdg16.04+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: backup_overview; Type: DATABASE; Schema: -; Owner: student
--

CREATE DATABASE backup_overview WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';


ALTER DATABASE backup_overview OWNER TO student;

\connect backup_overview

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: t; Type: TABLE; Schema: public; Owner: student
--

CREATE TABLE public.t (
    id numeric,
    s text
);


ALTER TABLE public.t OWNER TO student;

--
-- Data for Name: t; Type: TABLE DATA; Schema: public; Owner: student
--

COPY public.t (id, s) FROM stdin;
1	Hi there!
2	
3	\N
\.


--
-- PostgreSQL database dump complete
--

As an example let's restore the table in another database.

α=> CREATE DATABASE backup_overview2;
CREATE DATABASE
postgres$ pg_dump -d backup_overview --table=t | psql -d backup_overview2
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 3

student$ psql -d backup_overview2
β=> SELECT * FROM t;
 id |     s     
----+-----------
  1 | Hi there!
  2 | 
  3 | 
(3 rows)

β=> \q

Standalone backup

Starting from PostgreSQL 10 configuration parameters are set by default to appropriate values to use replication:

α=> SELECT name, setting
FROM pg_settings
WHERE name IN ('wal_level','max_wal_senders');
      name       | setting 
-----------------+---------
 max_wal_senders | 10
 wal_level       | replica
(2 rows)

In previous versions we would have to change the parameters.


We also need a record in the pg_hba.conf file which permits local connection by replication protocol. The record in already in place:

α=> SELECT type, database, user_name, address, auth_method 
FROM pg_hba_file_rules() 
WHERE database = '{replication}';
 type  |   database    | user_name |  address  | auth_method 
-------+---------------+-----------+-----------+-------------
 local | {replication} | {all}     |           | trust
 host  | {replication} | {all}     | 127.0.0.1 | md5
 host  | {replication} | {all}     | ::1       | md5
(3 rows)


So we are ready to start the pg_basebackup utility. We are using the plain format (by default). Destination of the backup is the PGDATA directory of the backup server (beta).

postgres$ rm -rf /var/lib/postgresql/11/beta/*
postgres$ pg_basebackup --pgdata=/var/lib/postgresql/11/beta

The backup server is already built and installed in the VM.


Let's check the contents of the directory:

postgres$ ls -l /var/lib/postgresql/11/beta
total 80
-rw------- 1 postgres postgres  226 фев 27 15:38 backup_label
drwx------ 8 postgres postgres 4096 фев 27 15:38 base
drwx------ 2 postgres postgres 4096 фев 27 15:38 global
drwx------ 2 postgres postgres 4096 фев 27 15:38 pg_commit_ts
drwx------ 2 postgres postgres 4096 фев 27 15:38 pg_dynshmem
drwx------ 4 postgres postgres 4096 фев 27 15:38 pg_logical
drwx------ 4 postgres postgres 4096 фев 27 15:38 pg_multixact
drwx------ 2 postgres postgres 4096 фев 27 15:38 pg_notify
drwx------ 2 postgres postgres 4096 фев 27 15:38 pg_replslot
drwx------ 2 postgres postgres 4096 фев 27 15:38 pg_serial
drwx------ 2 postgres postgres 4096 фев 27 15:38 pg_snapshots
drwx------ 2 postgres postgres 4096 фев 27 15:38 pg_stat
drwx------ 2 postgres postgres 4096 фев 27 15:38 pg_stat_tmp
drwx------ 2 postgres postgres 4096 фев 27 15:38 pg_subtrans
drwx------ 2 postgres postgres 4096 фев 27 15:38 pg_tblspc
drwx------ 2 postgres postgres 4096 фев 27 15:38 pg_twophase
-rw------- 1 postgres postgres    3 фев 27 15:38 PG_VERSION
drwx------ 3 postgres postgres 4096 фев 27 15:38 pg_wal
drwx------ 2 postgres postgres 4096 фев 27 15:38 pg_xact
-rw------- 1 postgres postgres   88 фев 27 15:38 postgresql.auto.conf

Restoring from a standalone backup

We are going to have both primary and backup servers running on the same hardware, so we need to change the port number. We'll do it in the ostgresql.auto.conf file:

postgres$ echo 'port = 5433' >> /var/lib/postgresql/11/beta/postgresql.auto.conf

And we are ready to start the server.

student$ sudo pg_ctlcluster 11 beta start

Now both servers are up and running independently. Here is the primary:

student$ psql -p 5432 -d backup_overview
α=> INSERT INTO t VALUES (4, 'I am primary');
INSERT 0 1
α=> SELECT * FROM t;
 id |      s       
----+--------------
  1 | Hi there!
  2 | 
  3 | 
  4 | I am primary
(4 rows)


And here is the backup server:

student$ psql -p 5433 -d backup_overview
β=> INSERT INTO t VALUES (4, 'I am backup');
INSERT 0 1
β=> SELECT * FROM t;
 id |      s      
----+-------------
  1 | Hi there!
  2 | 
  3 | 
  4 | I am backup
(4 rows)


The end of demonstration.