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
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
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
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.