A database and a table

α=> CREATE DATABASE backup_overview;
CREATE DATABASE
α=> \c backup_overview
You are now connected to database "backup_overview" as user "student".
α=> CREATE TABLE t(n integer);
CREATE TABLE
α=> INSERT INTO t VALUES (1), (2), (3);
INSERT 0 3

Logical backup

postgres$ pg_dump -f /var/lib/postgresql/backup_overview.dump -d backup_overview --create

Drop and restore

α=> \c postgres
You are now connected to database "postgres" as user "student".
α=> DROP DATABASE backup_overview;
DROP DATABASE
postgres$ psql -f /var/lib/postgresql/backup_overview.dump
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
CREATE DATABASE
ALTER DATABASE
You are now connected to database "backup_overview" as user "postgres".
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 3
α=> \c backup_overview
You are now connected to database "backup_overview" as user "student".
α=> SELECT * FROM t;
 n 
---
 1
 2
 3
(3 rows)

Physical backup

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

Changing the table

student$ psql -p 5432 -d backup_overview
α=> DELETE FROM t;
DELETE 3

Restore and check

Changing the port number:

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

Starting the server.

student$ sudo pg_ctlcluster 11 beta start
student$ psql -p 5433 -d backup_overview
β=> SELECT * FROM t;
 n 
---
 1
 2
 3
(3 rows)