=============================
Copy data only in text format
=============================
hr1dev=# select * from test.emp;
empno | ename | job | dob | deptno
-------+----------+-------+------------+--------
100 | Gouranga | Admin | 1983-01-10 | 10
101 | John | User | 1987-11-12 | 20
(2 rows)
hr1dev=# copy test.emp to '/tmp/emp.txt';
COPY 2
$ cat /tmp/emp.txt
100 Gouranga Admin 1983-01-10 10
101 John User 1987-11-12 20
hr1dev=# copy emp to '/tmp/empdata.txt';
COPY 1000100
hr1dev=# delete from emp;
DELETE 1000100
hr1dev=# select count(*) from emp;
count
-------
0
(1 row)
hr1dev=# copy emp from '/tmp/empdata.txt';
COPY 1000100
hr1dev=# select count(*) from emp;
count
---------
1000100
(1 row)
hr1dev=#
======================
export with delimiter:
======================
hr1dev=# copy emp to '/tmp/emp_del.txt' with delimiter '|';
COPY 1000100
hr1dev=#
======================
export with header:
======================
hr1dev=# copy emp to '/tmp/emp_head' with CSV HEADER;
COPY 1000100
hr1dev=#
$ more /tmp/emp_head
no,name
1,data1
2,data2
3,data3
4,data4
=======================================
export data for specific query:
=======================================
hr1dev=# copy (select * from emp where no>10000) to '/tmp/emp_query.txt';
COPY 990000
-----------------------------------------------------------
BACKUP AND RESTORE
-----------------------------------------------------------
In postgresql we have 2 levels of backups.
1. Logical backup
2. Physical backup (file system backup/hot backup/online backup/offline backup)
Logical Backup:
---------------
Dump file will be in the format of logical , it will not contains any physical files means configuration files/ data files/transaction logs. To perform logical backup we can use two utilities. These are :
a. pg_dump
b, pg_dumpall
a.pg_dump :
Using pg_dump, following are possible:
1. Single database backup at a time
2. Single schema,multiples schemas backup at a time
3. Single table,multiple tables backup at a time.
4. Dump can be taken in customized format means .tar or .gz directory formats.
7. Parallel jobs can be invoked
8. To restore, we can use traditional restore utility, this 10 times faster than the older utility.
Using pg_dump, we can't perform below activities:
1. we can't take global objects backup
2. Full server logical backup also not possible at a time.
b.pg_dumpall
Using pg_dumpall, following are possible:
1. Complete server backup at a time (all databases backup at a time)
2. Global objects backup only possible
We can't do the following using pg_dumpall utility:
1. Single database/table/schema backup not possible.
6. here we can't use traditional restore utility.
To restore, we can use 2 utilities:
-----------------------------------------
1.psql
2.pg_restore (traditional utility)
normally taking dump we have 2 ways:
1.plain format : we can able to read the dump file, what ever the statements you want to execute , you can simply execute as normal sql statements. while restoring you can't use pg_restore utility if dump in plain format.
2. If your dump in plain format , if you dump contains 10 tables backup, you can't restore single table from the dump.
3. To restore manually you have to copy that respective sql statements from the dump.
4. If dump in readable format, there might be number of chances for data misuse. this will be security compliance.
5. Always prefer to take dump in customized for, which is in binary format, not in the readable format. At the same time we can restore single table even-though dump contains multiple tables.
we can use pg_restore utility for restore as well.
Syntax: pg_dump -d db_name -t table_name
-- Taking backup in plain text format
$ /usr/local/pgsql/bin/pg_dump -t test.emp -d hr1dev > emp.sql
-bash-4.2$ pwd
/var/lib/pgsql/backups
-bash-4.2$ cat emp.sql
--
-- PostgreSQL database dump
--
-- Dumped from database version 11.0
-- Dumped by pg_dump version 11.0
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 = hr_tbs;
SET default_with_oids = false;
--
-- Name: emp; Type: TABLE; Schema: test; Owner: postgres; Tablespace: hr_tbs
--
CREATE TABLE test.emp (
empno integer NOT NULL,
ename character varying NOT NULL,
job character varying,
dob date,
deptno integer
);
ALTER TABLE test.emp OWNER TO postgres;
--
-- Data for Name: emp; Type: TABLE DATA; Schema: test; Owner: postgres
--
COPY test.emp (empno, ename, job, dob, deptno) FROM stdin;
100 Gouranga Admin 1983-01-10 10
101 John User 1987-11-12 20
\.
SET default_tablespace = '';
--
-- Name: emp emp_pkey; Type: CONSTRAINT; Schema: test; Owner: postgres
--
ALTER TABLE ONLY test.emp
ADD CONSTRAINT emp_pkey PRIMARY KEY (empno);
--
-- Name: TABLE emp; Type: ACL; Schema: test; Owner: postgres
--
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE test.emp TO gouranga;
--
-- PostgreSQL database dump complete
--
----------------------------------------------------------------------------------------------------------
=================
Restore from dump
=================
postgres=# \c hr1dev
You are now connected to database "hr1dev" as user "postgres".
hr1dev=# drop table test.emp;
ERROR: cannot drop table test.emp because other objects depend on it
DETAIL: materialized view test.emp_mv depends on table test.emp
HINT: Use DROP ... CASCADE to drop the dependent objects too.
hr1dev=# drop table test.emp CASCADE;
NOTICE: drop cascades to materialized view test.emp_mv
DROP TABLE
hr1dev=#
$ psql -d hr1dev -f emp.sql -o emp_hr1dev_imp.log
-f : filename where data exported in text format
-d : in which database to be imported
-o : output log file
hr1dev=# select * from test.emp;
empno | ename | job | dob | deptno
-------+----------+-------+------------+--------
100 | Gouranga | Admin | 1983-01-10 | 10
101 | John | User | 1987-11-12 | 20
(2 rows)
======================================
Export dump in customized format:
======================================
pg_dump -Fc -d hr1dev -f hr1dev.bin -v 2>dbbackup.log
Taking Dump in parallel format:
--------------------------------------
pg_dump -Fc -d hr1dev -f hr1dev.bin -v -j 2 2>dbbackup.log
Taking backup from remote system:
-----------------------------------------------
pg_dump -Fc -d hr1dev -f hr1dev.bin -v -j 2 -h 192.168.10.132 -p 5433 2>dbbackup.log
for location:
----------------
$ pg_dump -Fd -d hr1dev -f /fullpath/dbbackup -v -j 2 -h 192.168.10.132 -p 5433 2>dbbackup.log
example:
---------
$ pg_dump -Fc -d hr1dev -f hr1dev.bin -v 2>dbbackup.log
$ ll
total 22264
-rw-r--r--. 1 postgres postgres 3255 Dec 29 08:48 dbbackup.log
-rw-r--r--. 1 postgres postgres 22783616 Dec 29 08:48 hr1dev.bin
$ cat dbbackup.log
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined access methods
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrappers
pg_dump: reading user-defined foreign servers
pg_dump: reading default privileges
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading transforms
pg_dump: reading table inheritance information
pg_dump: reading event triggers
pg_dump: finding extension tables
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: finding the columns and types of table "test.sample"
pg_dump: finding the columns and types of table "test.sample2"
pg_dump: finding the columns and types of table "public.emp"
pg_dump: finding the columns and types of table "public.emp_mv"
pg_dump: finding the columns and types of table "test.emp"
pg_dump: flagging inherited columns in subtables
pg_dump: reading indexes
pg_dump: reading indexes for table "test.emp"
pg_dump: flagging indexes in partitioned tables
pg_dump: reading extended statistics
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading rewrite rules
pg_dump: reading policies
pg_dump: reading row security enabled for table "test.sample"
pg_dump: reading policies for table "test.sample"
pg_dump: reading row security enabled for table "test.sample2"
pg_dump: reading policies for table "test.sample2"
pg_dump: reading row security enabled for table "public.emp"
pg_dump: reading policies for table "public.emp"
pg_dump: reading row security enabled for table "public.emp_mv"
pg_dump: reading policies for table "public.emp_mv"
pg_dump: reading row security enabled for table "test.seq1"
pg_dump: reading policies for table "test.seq1"
pg_dump: reading row security enabled for table "test.emp"
pg_dump: reading policies for table "test.emp"
pg_dump: reading publications
pg_dump: reading publication membership
pg_dump: reading publication membership for table "test.sample"
pg_dump: reading publication membership for table "test.sample2"
pg_dump: reading publication membership for table "public.emp"
pg_dump: reading publication membership for table "test.emp"
pg_dump: reading subscriptions
pg_dump: reading large objects
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = on
pg_dump: saving search_path =
pg_dump: saving database definition
pg_dump: dumping contents of table "public.emp"
pg_dump: dumping contents of table "test.emp"
pg_dump: dumping contents of table "test.sample"
pg_dump: dumping contents of table "test.sample2"
$
Restore database from backup:
=============================
pg_restore -Fd -d hr1dev hr1dev.bin -v -j 2 2>db_hr1dev_restore.log
postgres=# drop database hr1dev;
DROP DATABASE
postgres=# \c hr1dev
FATAL: database "hr1dev" does not exist
Previous connection kept
postgres=#
postgres=# create database hr1dev;
CREATE DATABASE
$ pg_restore -Fc -d hr1dev hr1dev.bin -v -j 2 2>db_hr1dev_restore.log
$ cat db_hr1dev_restore.log
pg_restore: connecting to database for restore
pg_restore: processing item 3107 ENCODING ENCODING
pg_restore: processing item 3108 STDSTRINGS STDSTRINGS
pg_restore: processing item 3109 SEARCHPATH SEARCHPATH
pg_restore: processing item 3110 DATABASE hr1dev
pg_restore: processing item 6 SCHEMA test
pg_restore: creating SCHEMA "test"
pg_restore: processing item 199 TABLE emp
pg_restore: creating TABLE "public.emp"
pg_restore: processing item 200 MATERIALIZED VIEW emp_mv
pg_restore: creating MATERIALIZED VIEW "public.emp_mv"
pg_restore: processing item 202 TABLE emp
pg_restore: creating TABLE "test.emp"
pg_restore: processing item 197 TABLE sample
pg_restore: creating TABLE "test.sample"
pg_restore: processing item 198 TABLE sample2
pg_restore: creating TABLE "test.sample2"
pg_restore: processing item 201 SEQUENCE seq1
pg_restore: creating SEQUENCE "test.seq1"
pg_restore: entering main parallel loop
pg_restore: launching item 3101 TABLE DATA emp
pg_restore: launching item 3104 TABLE DATA emp
pg_restore: processing data for table "test.emp"
pg_restore: processing data for table "public.emp"
pg_restore: finished item 3104 TABLE DATA emp
pg_restore: launching item 3099 TABLE DATA sample
pg_restore: processing data for table "test.sample"
pg_restore: finished item 3099 TABLE DATA sample
pg_restore: launching item 3100 TABLE DATA sample2
pg_restore: processing data for table "test.sample2"
pg_restore: finished item 3100 TABLE DATA sample2
pg_restore: launching item 3113 SEQUENCE SET seq1
pg_restore: executing SEQUENCE SET seq1
pg_restore: finished item 3113 SEQUENCE SET seq1
pg_restore: launching item 2976 CONSTRAINT emp emp_pkey
pg_restore: creating CONSTRAINT "test.emp emp_pkey"
pg_restore: finished item 2976 CONSTRAINT emp emp_pkey
pg_restore: finished item 3101 TABLE DATA emp
pg_restore: launching item 3111 ACL SCHEMA test
pg_restore: launching item 3112 ACL TABLE emp
pg_restore: creating ACL "test.TABLE emp"
pg_restore: creating ACL "SCHEMA test"
pg_restore: finished item 3112 ACL TABLE emp
pg_restore: finished item 3111 ACL SCHEMA test
pg_restore: launching item 3102 MATERIALIZED VIEW DATA emp_mv
pg_restore: creating MATERIALIZED VIEW DATA "public.emp_mv"
pg_restore: finished item 3102 MATERIALIZED VIEW DATA emp_mv
pg_restore: finished main parallel loop
Testing:
postgres=# \c hr1dev
You are now connected to database "hr1dev" as user "postgres".
hr1dev=# \dt test.*
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
test | emp | table | postgres
test | sample | table | gouranga
test | sample2 | table | postgres
(3 rows)
hr1dev=#
Example-2:
pg_dump -Fd -d demodb -f dbbackup -v -j 2 2>dbdemo_backup.log
postgres=# drop database demodb;
DROP DATABASE
pg_restore -Fd -d demodb dbbackup -v -j 2 2>db_demodb_restore.log
postgres=# create database demodb;
CREATE DATABASE
postgres=# \q
-bash-4.2$ pg_restore -Fd -d demodb dbbackup -v -j 2 2>db_demodb_restore.log
-bash-4.2$
-bash-4.2$ cat db_demodb_restore.log
pg_restore: connecting to database for restore
pg_restore: processing item 3079 ENCODING ENCODING
pg_restore: processing item 3080 STDSTRINGS STDSTRINGS
pg_restore: processing item 3081 SEARCHPATH SEARCHPATH
pg_restore: processing item 3082 DATABASE demodb
pg_restore: processing item 196 TABLE emp
pg_restore: creating TABLE "public.emp"
pg_restore: entering main parallel loop
pg_restore: launching item 3076 TABLE DATA emp
pg_restore: processing data for table "public.emp"
pg_restore: finished item 3076 TABLE DATA emp
pg_restore: finished main parallel loop
-bash-4.2$ psql
psql (11.0)
Type "help" for help.
postgres=# \c demodb
You are now connected to database "demodb" as user "postgres".
demodb=# select count(1) from emp;
count
-------
50
(1 row)
demodb=#
==============================================
To take complete server level backup:
==============================================
1. parallel not possible
2. only plain format will work.
3. use pg_dumpall
To Take backup:
----------------
$ pg_dumpall>fullserverbackup.sql
$ ls -ltr fullserverbackup.sql
-rw-r--r--. 1 postgres postgres 17786563 Dec 29 09:44 fullserverbackup.sql
To Restore
---------------
$ psql -f fullserverbackup.sql -o fullserverrestore.log
This dump will contains users and user access rights information and table spaces information, this is very rare . Even-though you are taking backup of full server, if required point in-time recovery possible. Only with physical backup only we can able to do point in-time recovery.
PHYSICAL BACKUP:
1. File system backup: after stopping application and jobs related to databases, we will apply check point and we will copy data directory.
2. Hot backup:
3. offline backup : stop services and by using any Linux command copy data directory to any other location.
4. online backup: no need to stop jobs, no need to stop services, simply you can take the backup by using an utility called pg_basebackup.
pg_basebackup:
===============
It will contain exact copy of our data directory with proper check point. it will gives consistency.
While triggering this one first it will apply check point , and it will start actual backup.
To take this backup you need empty directory always.
For plain database, you can take the full backup within the server, but if you have table-spaces, then you are going to take backup,
simply data directory will be backed up to respective directory. but table-space location will be trying to backed up to existing location
only, if that is empty, backup will success, if that is not empty we have to remap.
Example:
List tablespaces:
postgres=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+----------+-------------------+-------------------+---------+--------+-------------
hr_tbs | postgres | /usr/pgsql/hr_tbs | | | 16 kB |
pg_default | postgres | | | | 207 MB |
pg_global | postgres | | | | 574 kB |
(3 rows)
postgres=#
$ pg_basebackup -D /var/lib/pgsql/base_backup/ -X fetch -P -T /usr/pgsql/hr_tbs=/usr/pgsql/hr_tbs_new
229205/229205 kB (100%), 2/2 tablespaces
$
You can backup in .tar format also:
$ pg_basebackup -D /var/lib/pgsql/base_backup/ -X fetch -Ft -P -T /usr/pgsql/hr_tbs=/usr/pgsql/hr_tbs_new
229206/229206 kB (100%), 2/2 tablespaces
$
Note: Ensure 'base_backup' must be empty.
$ pwd
/var/lib/pgsql
$ cd base_backup/
-bash-4.2$ ls -ltr
total 229212
-rw-------. 1 postgres postgres 19968 Dec 29 10:18 24634.tar
-rw-------. 1 postgres postgres 234689024 Dec 29 10:18 base.tar
$
You can see here, all folders backed up:
$ pwd
/var/lib/pgsql/base_backup
$ ls -ltr
total 60
-rw-------. 1 postgres postgres 226 Dec 29 10:14 backup_label
drwx------. 2 postgres postgres 6 Dec 29 10:14 pg_dynshmem
drwx------. 2 postgres postgres 6 Dec 29 10:14 pg_commit_ts
drwx------. 2 postgres postgres 6 Dec 29 10:14 pg_twophase
drwx------. 2 postgres postgres 6 Dec 29 10:14 pg_subtrans
drwx------. 2 postgres postgres 6 Dec 29 10:14 pg_snapshots
drwx------. 2 postgres postgres 6 Dec 29 10:14 pg_serial
drwx------. 2 postgres postgres 6 Dec 29 10:14 pg_notify
drwx------. 4 postgres postgres 34 Dec 29 10:14 pg_multixact
drwx------. 7 postgres postgres 62 Dec 29 10:14 base
drwx------. 2 postgres postgres 18 Dec 29 10:14 pg_tblspc
drwx------. 2 postgres postgres 6 Dec 29 10:14 pg_replslot
drwx------. 2 postgres postgres 6 Dec 29 10:14 pg_stat
-rw-------. 1 postgres postgres 23806 Dec 29 10:14 postgresql.conf
-rw-------. 1 postgres postgres 88 Dec 29 10:14 postgresql.auto.conf
drwx------. 2 postgres postgres 17 Dec 29 10:14 pg_xact
-rw-------. 1 postgres postgres 3 Dec 29 10:14 PG_VERSION
drwx------. 2 postgres postgres 6 Dec 29 10:14 pg_stat_tmp
drwx------. 4 postgres postgres 65 Dec 29 10:14 pg_logical
-rw-------. 1 postgres postgres 1636 Dec 29 10:14 pg_ident.conf
-rw-------. 1 postgres postgres 4513 Dec 29 10:14 pg_hba.conf
drwx------. 2 postgres postgres 4096 Dec 29 10:14 pg_log
drwx------. 3 postgres postgres 28 Dec 29 10:14 hr_tbs_new
drwx------. 2 postgres postgres 6 Dec 29 10:14 hr_tbs
-rw-------. 1 postgres postgres 47 Dec 29 10:14 current_logfiles
drwx------. 2 postgres postgres 4096 Dec 29 10:14 global
drwx------. 3 postgres postgres 58 Dec 29 10:14 pg_wal
-bash-4.2$
For TAR format no need to mention table-space remapping, it will be create number of files based on number of table-spaces.
$ pwd
/var/lib/pgsql/backup
$ pg_basebackup -D /var/lib/pgsql/backup/ -X fetch -Ft -P
229206/229206 kB (100%), 2/2 tablespaces
$
$ ls -ltr
total 229212
-rw-------. 1 postgres postgres 19968 Dec 29 10:23 24634.tar
-rw-------. 1 postgres postgres 234689536 Dec 29 10:23 base.tar
$ pwd
/var/lib/pgsql/backup
================================
Restore from Full Backup
================================
[postgres@example02 backup]$ tar -xvf base.tar
[postgres@example02 backup]$ rm base.tar
[postgres@example02 backup]$ tar -xvf 16613.tar
PG_12_201909212/
[postgres@example02 backup]$ cat tablespace_map
16613 /var/lib/pgsql/hr_tbs_new
update tablespace_map file wit new table space location.
change port in postgresql.conf file of backup directory.
[postgres@example02 backup]$ chmod 0700 /var/lib/pgsql/backup/
[postgres@example02 backup]$ pg_ctl -D /var/lib/pgsql/backup start
waiting for server to start.... 3372 2019-12-24 17:58:55 PST 1LOG: starting PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
3372 2019-12-24 17:58:55 PST 2LOG: listening on IPv6 address "::1", port 5433
3372 2019-12-24 17:58:55 PST 3LOG: listening on IPv4 address "127.0.0.1", port 5433
3372 2019-12-24 17:58:55 PST 4LOG: listening on IPv4 address "192.168.38.132", port 5433
3372 2019-12-24 17:58:55 PST 5LOG: listening on Unix socket "/tmp/.s.PGSQL.5433"
3372 2019-12-24 17:58:55 PST 6LOG: redirecting log output to logging collector process
3372 2019-12-24 17:58:55 PST 7HINT: Future log output will appear in directory "log".
done
server started
[postgres@example02 backup]$ ps -ef|grep postgres
root 2815 2784 0 16:47 pts/0 00:00:00 su - postgres
postgres 2816 2815 0 16:47 pts/0 00:00:00 -bash
postgres 2851 1 0 16:47 ? 00:00:00 /usr/local/pgsql/bin/postgres
postgres 2852 2851 0 16:47 ? 00:00:00 postgres: logger
postgres 2854 2851 0 16:47 ? 00:00:00 postgres: checkpointer
postgres 2855 2851 0 16:47 ? 00:00:00 postgres: background writer
postgres 2856 2851 0 16:47 ? 00:00:00 postgres: walwriter
postgres 2857 2851 0 16:47 ? 00:00:00 postgres: stats collector
postgres 2858 2851 0 16:47 ? 00:00:00 postgres: logical replication launcher
postgres 3372 1 0 17:58 ? 00:00:00 /usr/local/pgsql/bin/postgres -D /var/lib/pgsql/backup
postgres 3373 3372 0 17:58 ? 00:00:00 postgres: logger
postgres 3375 3372 0 17:58 ? 00:00:00 postgres: checkpointer
postgres 3376 3372 0 17:58 ? 00:00:00 postgres: background writer
postgres 3377 3372 0 17:58 ? 00:00:00 postgres: walwriter
postgres 3378 3372 0 17:58 ? 00:00:00 postgres: stats collector
postgres 3379 3372 0 17:58 ? 00:00:00 postgres: logical replication launcher
postgres 3380 2816 0 17:59 pts/0 00:00:00 ps -ef
postgres 3381 2816 0 17:59 pts/0 00:00:00 grep --color=auto postgres
[postgres@example02 backup]$
Now 2 instances running in the same server.
with same binaries, different data directories, different ports we can start number of instances.
[postgres@example02 backup]$ psql -p5433
psql (12.0)
Type "help" for help.
postgres=# show data_directory;
data_directory
-----------------------
/var/lib/pgsql/backup
(1 row)
postgres=#
[postgres@example02 backup]$ pg_ctl -D /var/lib/pgsql/DATA/ status
pg_ctl: server is running (PID: 2851)
/usr/local/pgsql/bin/postgres
[postgres@example02 backup]$ pg_ctl -D /var/lib/pgsql/backup/ status
pg_ctl: server is running (PID: 3372)
/usr/local/pgsql/bin/postgres "-D" "/var/lib/pgsql/backup"
[postgres@example02 backup]$
Anywhere backup strategy will be physical only. daily full backup.
There is no incremental concept in PostgreSQL.
They will schedule script to create folder before backup with the date.
Copy data only in text format
=============================
hr1dev=# select * from test.emp;
empno | ename | job | dob | deptno
-------+----------+-------+------------+--------
100 | Gouranga | Admin | 1983-01-10 | 10
101 | John | User | 1987-11-12 | 20
(2 rows)
hr1dev=# copy test.emp to '/tmp/emp.txt';
COPY 2
$ cat /tmp/emp.txt
100 Gouranga Admin 1983-01-10 10
101 John User 1987-11-12 20
hr1dev=# copy emp to '/tmp/empdata.txt';
COPY 1000100
hr1dev=# delete from emp;
DELETE 1000100
hr1dev=# select count(*) from emp;
count
-------
0
(1 row)
hr1dev=# copy emp from '/tmp/empdata.txt';
COPY 1000100
hr1dev=# select count(*) from emp;
count
---------
1000100
(1 row)
hr1dev=#
======================
export with delimiter:
======================
hr1dev=# copy emp to '/tmp/emp_del.txt' with delimiter '|';
COPY 1000100
hr1dev=#
======================
export with header:
======================
hr1dev=# copy emp to '/tmp/emp_head' with CSV HEADER;
COPY 1000100
hr1dev=#
$ more /tmp/emp_head
no,name
1,data1
2,data2
3,data3
4,data4
=======================================
export data for specific query:
=======================================
hr1dev=# copy (select * from emp where no>10000) to '/tmp/emp_query.txt';
COPY 990000
-----------------------------------------------------------
BACKUP AND RESTORE
-----------------------------------------------------------
In postgresql we have 2 levels of backups.
1. Logical backup
2. Physical backup (file system backup/hot backup/online backup/offline backup)
Logical Backup:
---------------
Dump file will be in the format of logical , it will not contains any physical files means configuration files/ data files/transaction logs. To perform logical backup we can use two utilities. These are :
a. pg_dump
b, pg_dumpall
a.pg_dump :
Using pg_dump, following are possible:
1. Single database backup at a time
2. Single schema,multiples schemas backup at a time
3. Single table,multiple tables backup at a time.
4. Dump can be taken in customized format means .tar or .gz directory formats.
7. Parallel jobs can be invoked
8. To restore, we can use traditional restore utility, this 10 times faster than the older utility.
Using pg_dump, we can't perform below activities:
1. we can't take global objects backup
2. Full server logical backup also not possible at a time.
b.pg_dumpall
Using pg_dumpall, following are possible:
1. Complete server backup at a time (all databases backup at a time)
2. Global objects backup only possible
We can't do the following using pg_dumpall utility:
1. Single database/table/schema backup not possible.
2. Taking dump in customized format is not possible.
5. we can't perform parallel jobs6. here we can't use traditional restore utility.
To restore, we can use 2 utilities:
-----------------------------------------
1.psql
2.pg_restore (traditional utility)
normally taking dump we have 2 ways:
1.plain format : we can able to read the dump file, what ever the statements you want to execute , you can simply execute as normal sql statements. while restoring you can't use pg_restore utility if dump in plain format.
2. If your dump in plain format , if you dump contains 10 tables backup, you can't restore single table from the dump.
3. To restore manually you have to copy that respective sql statements from the dump.
4. If dump in readable format, there might be number of chances for data misuse. this will be security compliance.
5. Always prefer to take dump in customized for, which is in binary format, not in the readable format. At the same time we can restore single table even-though dump contains multiple tables.
we can use pg_restore utility for restore as well.
Syntax: pg_dump -d db_name -t table_name
-- Taking backup in plain text format
$ /usr/local/pgsql/bin/pg_dump -t test.emp -d hr1dev > emp.sql
-bash-4.2$ pwd
/var/lib/pgsql/backups
-bash-4.2$ cat emp.sql
--
-- PostgreSQL database dump
--
-- Dumped from database version 11.0
-- Dumped by pg_dump version 11.0
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 = hr_tbs;
SET default_with_oids = false;
--
-- Name: emp; Type: TABLE; Schema: test; Owner: postgres; Tablespace: hr_tbs
--
CREATE TABLE test.emp (
empno integer NOT NULL,
ename character varying NOT NULL,
job character varying,
dob date,
deptno integer
);
ALTER TABLE test.emp OWNER TO postgres;
--
-- Data for Name: emp; Type: TABLE DATA; Schema: test; Owner: postgres
--
COPY test.emp (empno, ename, job, dob, deptno) FROM stdin;
100 Gouranga Admin 1983-01-10 10
101 John User 1987-11-12 20
\.
SET default_tablespace = '';
--
-- Name: emp emp_pkey; Type: CONSTRAINT; Schema: test; Owner: postgres
--
ALTER TABLE ONLY test.emp
ADD CONSTRAINT emp_pkey PRIMARY KEY (empno);
--
-- Name: TABLE emp; Type: ACL; Schema: test; Owner: postgres
--
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE test.emp TO gouranga;
--
-- PostgreSQL database dump complete
--
----------------------------------------------------------------------------------------------------------
=================
Restore from dump
=================
postgres=# \c hr1dev
You are now connected to database "hr1dev" as user "postgres".
hr1dev=# drop table test.emp;
ERROR: cannot drop table test.emp because other objects depend on it
DETAIL: materialized view test.emp_mv depends on table test.emp
HINT: Use DROP ... CASCADE to drop the dependent objects too.
hr1dev=# drop table test.emp CASCADE;
NOTICE: drop cascades to materialized view test.emp_mv
DROP TABLE
hr1dev=#
$ psql -d hr1dev -f emp.sql -o emp_hr1dev_imp.log
-f : filename where data exported in text format
-d : in which database to be imported
-o : output log file
hr1dev=# select * from test.emp;
empno | ename | job | dob | deptno
-------+----------+-------+------------+--------
100 | Gouranga | Admin | 1983-01-10 | 10
101 | John | User | 1987-11-12 | 20
(2 rows)
======================================
Export dump in customized format:
======================================
pg_dump -Fc -d hr1dev -f hr1dev.bin -v 2>dbbackup.log
Taking Dump in parallel format:
--------------------------------------
pg_dump -Fc -d hr1dev -f hr1dev.bin -v -j 2 2>dbbackup.log
Taking backup from remote system:
-----------------------------------------------
pg_dump -Fc -d hr1dev -f hr1dev.bin -v -j 2 -h 192.168.10.132 -p 5433 2>dbbackup.log
for location:
----------------
$ pg_dump -Fd -d hr1dev -f /fullpath/dbbackup -v -j 2 -h 192.168.10.132 -p 5433 2>dbbackup.log
example:
---------
$ pg_dump -Fc -d hr1dev -f hr1dev.bin -v 2>dbbackup.log
$ ll
total 22264
-rw-r--r--. 1 postgres postgres 3255 Dec 29 08:48 dbbackup.log
-rw-r--r--. 1 postgres postgres 22783616 Dec 29 08:48 hr1dev.bin
$ cat dbbackup.log
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined access methods
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrappers
pg_dump: reading user-defined foreign servers
pg_dump: reading default privileges
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading transforms
pg_dump: reading table inheritance information
pg_dump: reading event triggers
pg_dump: finding extension tables
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: finding the columns and types of table "test.sample"
pg_dump: finding the columns and types of table "test.sample2"
pg_dump: finding the columns and types of table "public.emp"
pg_dump: finding the columns and types of table "public.emp_mv"
pg_dump: finding the columns and types of table "test.emp"
pg_dump: flagging inherited columns in subtables
pg_dump: reading indexes
pg_dump: reading indexes for table "test.emp"
pg_dump: flagging indexes in partitioned tables
pg_dump: reading extended statistics
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading rewrite rules
pg_dump: reading policies
pg_dump: reading row security enabled for table "test.sample"
pg_dump: reading policies for table "test.sample"
pg_dump: reading row security enabled for table "test.sample2"
pg_dump: reading policies for table "test.sample2"
pg_dump: reading row security enabled for table "public.emp"
pg_dump: reading policies for table "public.emp"
pg_dump: reading row security enabled for table "public.emp_mv"
pg_dump: reading policies for table "public.emp_mv"
pg_dump: reading row security enabled for table "test.seq1"
pg_dump: reading policies for table "test.seq1"
pg_dump: reading row security enabled for table "test.emp"
pg_dump: reading policies for table "test.emp"
pg_dump: reading publications
pg_dump: reading publication membership
pg_dump: reading publication membership for table "test.sample"
pg_dump: reading publication membership for table "test.sample2"
pg_dump: reading publication membership for table "public.emp"
pg_dump: reading publication membership for table "test.emp"
pg_dump: reading subscriptions
pg_dump: reading large objects
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = on
pg_dump: saving search_path =
pg_dump: saving database definition
pg_dump: dumping contents of table "public.emp"
pg_dump: dumping contents of table "test.emp"
pg_dump: dumping contents of table "test.sample"
pg_dump: dumping contents of table "test.sample2"
$
Restore database from backup:
=============================
pg_restore -Fd -d hr1dev hr1dev.bin -v -j 2 2>db_hr1dev_restore.log
postgres=# drop database hr1dev;
DROP DATABASE
postgres=# \c hr1dev
FATAL: database "hr1dev" does not exist
Previous connection kept
postgres=#
postgres=# create database hr1dev;
CREATE DATABASE
$ pg_restore -Fc -d hr1dev hr1dev.bin -v -j 2 2>db_hr1dev_restore.log
$ cat db_hr1dev_restore.log
pg_restore: connecting to database for restore
pg_restore: processing item 3107 ENCODING ENCODING
pg_restore: processing item 3108 STDSTRINGS STDSTRINGS
pg_restore: processing item 3109 SEARCHPATH SEARCHPATH
pg_restore: processing item 3110 DATABASE hr1dev
pg_restore: processing item 6 SCHEMA test
pg_restore: creating SCHEMA "test"
pg_restore: processing item 199 TABLE emp
pg_restore: creating TABLE "public.emp"
pg_restore: processing item 200 MATERIALIZED VIEW emp_mv
pg_restore: creating MATERIALIZED VIEW "public.emp_mv"
pg_restore: processing item 202 TABLE emp
pg_restore: creating TABLE "test.emp"
pg_restore: processing item 197 TABLE sample
pg_restore: creating TABLE "test.sample"
pg_restore: processing item 198 TABLE sample2
pg_restore: creating TABLE "test.sample2"
pg_restore: processing item 201 SEQUENCE seq1
pg_restore: creating SEQUENCE "test.seq1"
pg_restore: entering main parallel loop
pg_restore: launching item 3101 TABLE DATA emp
pg_restore: launching item 3104 TABLE DATA emp
pg_restore: processing data for table "test.emp"
pg_restore: processing data for table "public.emp"
pg_restore: finished item 3104 TABLE DATA emp
pg_restore: launching item 3099 TABLE DATA sample
pg_restore: processing data for table "test.sample"
pg_restore: finished item 3099 TABLE DATA sample
pg_restore: launching item 3100 TABLE DATA sample2
pg_restore: processing data for table "test.sample2"
pg_restore: finished item 3100 TABLE DATA sample2
pg_restore: launching item 3113 SEQUENCE SET seq1
pg_restore: executing SEQUENCE SET seq1
pg_restore: finished item 3113 SEQUENCE SET seq1
pg_restore: launching item 2976 CONSTRAINT emp emp_pkey
pg_restore: creating CONSTRAINT "test.emp emp_pkey"
pg_restore: finished item 2976 CONSTRAINT emp emp_pkey
pg_restore: finished item 3101 TABLE DATA emp
pg_restore: launching item 3111 ACL SCHEMA test
pg_restore: launching item 3112 ACL TABLE emp
pg_restore: creating ACL "test.TABLE emp"
pg_restore: creating ACL "SCHEMA test"
pg_restore: finished item 3112 ACL TABLE emp
pg_restore: finished item 3111 ACL SCHEMA test
pg_restore: launching item 3102 MATERIALIZED VIEW DATA emp_mv
pg_restore: creating MATERIALIZED VIEW DATA "public.emp_mv"
pg_restore: finished item 3102 MATERIALIZED VIEW DATA emp_mv
pg_restore: finished main parallel loop
Testing:
postgres=# \c hr1dev
You are now connected to database "hr1dev" as user "postgres".
hr1dev=# \dt test.*
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
test | emp | table | postgres
test | sample | table | gouranga
test | sample2 | table | postgres
(3 rows)
hr1dev=#
Example-2:
pg_dump -Fd -d demodb -f dbbackup -v -j 2 2>dbdemo_backup.log
postgres=# drop database demodb;
DROP DATABASE
pg_restore -Fd -d demodb dbbackup -v -j 2 2>db_demodb_restore.log
postgres=# create database demodb;
CREATE DATABASE
postgres=# \q
-bash-4.2$ pg_restore -Fd -d demodb dbbackup -v -j 2 2>db_demodb_restore.log
-bash-4.2$
-bash-4.2$ cat db_demodb_restore.log
pg_restore: connecting to database for restore
pg_restore: processing item 3079 ENCODING ENCODING
pg_restore: processing item 3080 STDSTRINGS STDSTRINGS
pg_restore: processing item 3081 SEARCHPATH SEARCHPATH
pg_restore: processing item 3082 DATABASE demodb
pg_restore: processing item 196 TABLE emp
pg_restore: creating TABLE "public.emp"
pg_restore: entering main parallel loop
pg_restore: launching item 3076 TABLE DATA emp
pg_restore: processing data for table "public.emp"
pg_restore: finished item 3076 TABLE DATA emp
pg_restore: finished main parallel loop
-bash-4.2$ psql
psql (11.0)
Type "help" for help.
postgres=# \c demodb
You are now connected to database "demodb" as user "postgres".
demodb=# select count(1) from emp;
count
-------
50
(1 row)
demodb=#
==============================================
To take complete server level backup:
==============================================
1. parallel not possible
2. only plain format will work.
3. use pg_dumpall
To Take backup:
----------------
$ pg_dumpall>fullserverbackup.sql
$ ls -ltr fullserverbackup.sql
-rw-r--r--. 1 postgres postgres 17786563 Dec 29 09:44 fullserverbackup.sql
To Restore
---------------
$ psql -f fullserverbackup.sql -o fullserverrestore.log
This dump will contains users and user access rights information and table spaces information, this is very rare . Even-though you are taking backup of full server, if required point in-time recovery possible. Only with physical backup only we can able to do point in-time recovery.
PHYSICAL BACKUP:
1. File system backup: after stopping application and jobs related to databases, we will apply check point and we will copy data directory.
2. Hot backup:
3. offline backup : stop services and by using any Linux command copy data directory to any other location.
4. online backup: no need to stop jobs, no need to stop services, simply you can take the backup by using an utility called pg_basebackup.
pg_basebackup:
===============
It will contain exact copy of our data directory with proper check point. it will gives consistency.
While triggering this one first it will apply check point , and it will start actual backup.
To take this backup you need empty directory always.
For plain database, you can take the full backup within the server, but if you have table-spaces, then you are going to take backup,
simply data directory will be backed up to respective directory. but table-space location will be trying to backed up to existing location
only, if that is empty, backup will success, if that is not empty we have to remap.
Example:
List tablespaces:
postgres=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+----------+-------------------+-------------------+---------+--------+-------------
hr_tbs | postgres | /usr/pgsql/hr_tbs | | | 16 kB |
pg_default | postgres | | | | 207 MB |
pg_global | postgres | | | | 574 kB |
(3 rows)
postgres=#
$ pg_basebackup -D /var/lib/pgsql/base_backup/ -X fetch -P -T /usr/pgsql/hr_tbs=/usr/pgsql/hr_tbs_new
229205/229205 kB (100%), 2/2 tablespaces
$
You can backup in .tar format also:
$ pg_basebackup -D /var/lib/pgsql/base_backup/ -X fetch -Ft -P -T /usr/pgsql/hr_tbs=/usr/pgsql/hr_tbs_new
229206/229206 kB (100%), 2/2 tablespaces
$
Note: Ensure 'base_backup' must be empty.
$ pwd
/var/lib/pgsql
$ cd base_backup/
-bash-4.2$ ls -ltr
total 229212
-rw-------. 1 postgres postgres 19968 Dec 29 10:18 24634.tar
-rw-------. 1 postgres postgres 234689024 Dec 29 10:18 base.tar
$
You can see here, all folders backed up:
$ pwd
/var/lib/pgsql/base_backup
$ ls -ltr
total 60
-rw-------. 1 postgres postgres 226 Dec 29 10:14 backup_label
drwx------. 2 postgres postgres 6 Dec 29 10:14 pg_dynshmem
drwx------. 2 postgres postgres 6 Dec 29 10:14 pg_commit_ts
drwx------. 2 postgres postgres 6 Dec 29 10:14 pg_twophase
drwx------. 2 postgres postgres 6 Dec 29 10:14 pg_subtrans
drwx------. 2 postgres postgres 6 Dec 29 10:14 pg_snapshots
drwx------. 2 postgres postgres 6 Dec 29 10:14 pg_serial
drwx------. 2 postgres postgres 6 Dec 29 10:14 pg_notify
drwx------. 4 postgres postgres 34 Dec 29 10:14 pg_multixact
drwx------. 7 postgres postgres 62 Dec 29 10:14 base
drwx------. 2 postgres postgres 18 Dec 29 10:14 pg_tblspc
drwx------. 2 postgres postgres 6 Dec 29 10:14 pg_replslot
drwx------. 2 postgres postgres 6 Dec 29 10:14 pg_stat
-rw-------. 1 postgres postgres 23806 Dec 29 10:14 postgresql.conf
-rw-------. 1 postgres postgres 88 Dec 29 10:14 postgresql.auto.conf
drwx------. 2 postgres postgres 17 Dec 29 10:14 pg_xact
-rw-------. 1 postgres postgres 3 Dec 29 10:14 PG_VERSION
drwx------. 2 postgres postgres 6 Dec 29 10:14 pg_stat_tmp
drwx------. 4 postgres postgres 65 Dec 29 10:14 pg_logical
-rw-------. 1 postgres postgres 1636 Dec 29 10:14 pg_ident.conf
-rw-------. 1 postgres postgres 4513 Dec 29 10:14 pg_hba.conf
drwx------. 2 postgres postgres 4096 Dec 29 10:14 pg_log
drwx------. 3 postgres postgres 28 Dec 29 10:14 hr_tbs_new
drwx------. 2 postgres postgres 6 Dec 29 10:14 hr_tbs
-rw-------. 1 postgres postgres 47 Dec 29 10:14 current_logfiles
drwx------. 2 postgres postgres 4096 Dec 29 10:14 global
drwx------. 3 postgres postgres 58 Dec 29 10:14 pg_wal
-bash-4.2$
For TAR format no need to mention table-space remapping, it will be create number of files based on number of table-spaces.
$ pwd
/var/lib/pgsql/backup
$ pg_basebackup -D /var/lib/pgsql/backup/ -X fetch -Ft -P
229206/229206 kB (100%), 2/2 tablespaces
$
$ ls -ltr
total 229212
-rw-------. 1 postgres postgres 19968 Dec 29 10:23 24634.tar
-rw-------. 1 postgres postgres 234689536 Dec 29 10:23 base.tar
$ pwd
/var/lib/pgsql/backup
================================
Restore from Full Backup
================================
[postgres@example02 backup]$ tar -xvf base.tar
[postgres@example02 backup]$ rm base.tar
[postgres@example02 backup]$ tar -xvf 16613.tar
PG_12_201909212/
[postgres@example02 backup]$ cat tablespace_map
16613 /var/lib/pgsql/hr_tbs_new
update tablespace_map file wit new table space location.
change port in postgresql.conf file of backup directory.
[postgres@example02 backup]$ chmod 0700 /var/lib/pgsql/backup/
[postgres@example02 backup]$ pg_ctl -D /var/lib/pgsql/backup start
waiting for server to start.... 3372 2019-12-24 17:58:55 PST 1LOG: starting PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
3372 2019-12-24 17:58:55 PST 2LOG: listening on IPv6 address "::1", port 5433
3372 2019-12-24 17:58:55 PST 3LOG: listening on IPv4 address "127.0.0.1", port 5433
3372 2019-12-24 17:58:55 PST 4LOG: listening on IPv4 address "192.168.38.132", port 5433
3372 2019-12-24 17:58:55 PST 5LOG: listening on Unix socket "/tmp/.s.PGSQL.5433"
3372 2019-12-24 17:58:55 PST 6LOG: redirecting log output to logging collector process
3372 2019-12-24 17:58:55 PST 7HINT: Future log output will appear in directory "log".
done
server started
[postgres@example02 backup]$ ps -ef|grep postgres
root 2815 2784 0 16:47 pts/0 00:00:00 su - postgres
postgres 2816 2815 0 16:47 pts/0 00:00:00 -bash
postgres 2851 1 0 16:47 ? 00:00:00 /usr/local/pgsql/bin/postgres
postgres 2852 2851 0 16:47 ? 00:00:00 postgres: logger
postgres 2854 2851 0 16:47 ? 00:00:00 postgres: checkpointer
postgres 2855 2851 0 16:47 ? 00:00:00 postgres: background writer
postgres 2856 2851 0 16:47 ? 00:00:00 postgres: walwriter
postgres 2857 2851 0 16:47 ? 00:00:00 postgres: stats collector
postgres 2858 2851 0 16:47 ? 00:00:00 postgres: logical replication launcher
postgres 3372 1 0 17:58 ? 00:00:00 /usr/local/pgsql/bin/postgres -D /var/lib/pgsql/backup
postgres 3373 3372 0 17:58 ? 00:00:00 postgres: logger
postgres 3375 3372 0 17:58 ? 00:00:00 postgres: checkpointer
postgres 3376 3372 0 17:58 ? 00:00:00 postgres: background writer
postgres 3377 3372 0 17:58 ? 00:00:00 postgres: walwriter
postgres 3378 3372 0 17:58 ? 00:00:00 postgres: stats collector
postgres 3379 3372 0 17:58 ? 00:00:00 postgres: logical replication launcher
postgres 3380 2816 0 17:59 pts/0 00:00:00 ps -ef
postgres 3381 2816 0 17:59 pts/0 00:00:00 grep --color=auto postgres
[postgres@example02 backup]$
Now 2 instances running in the same server.
with same binaries, different data directories, different ports we can start number of instances.
[postgres@example02 backup]$ psql -p5433
psql (12.0)
Type "help" for help.
postgres=# show data_directory;
data_directory
-----------------------
/var/lib/pgsql/backup
(1 row)
postgres=#
[postgres@example02 backup]$ pg_ctl -D /var/lib/pgsql/DATA/ status
pg_ctl: server is running (PID: 2851)
/usr/local/pgsql/bin/postgres
[postgres@example02 backup]$ pg_ctl -D /var/lib/pgsql/backup/ status
pg_ctl: server is running (PID: 3372)
/usr/local/pgsql/bin/postgres "-D" "/var/lib/pgsql/backup"
[postgres@example02 backup]$
Anywhere backup strategy will be physical only. daily full backup.
There is no incremental concept in PostgreSQL.
They will schedule script to create folder before backup with the date.
No comments:
Post a Comment