1. Backup Single Database
pg_dump -h [host address] -Fc -o -U [database user] <database name> > [dump file]
pg_dump -h 10.10.10.8 -p 5432 -U admin teguhth > teguhth_remote.sql
restore
psql -h 10.10.10.8 -p 5432 -U admin teguhth < teguhth_remote.sql
Backup
pg_dump teguhth > teguhth_singledb.sql
-bash-4.2$ pg_dump teguhth > teguhth_singledb.sql
[root@teguhth edb]# su - postgres
Last login: Tue Jul 25 13:09:57 WIB 2023 on pts/1
-bash-4.2$ cd 14/backups/
-bash-4.2$ pwd
/var/lib/pgsql/14/backups
-bash-4.2$
-bash-4.2$ ls
-bash-4.2$
-bash-4.2$ pg_dump teguhth > teguhth_singledb.sql
-bash-4.2$
-bash-4.2$ ls
teguhth_singledb.sql
-bash-4.2$ ls -l
total 8
-rw-r--r-- 1 postgres postgres 7023 Jul 25 13:12 teguhth_singledb.sql
-bash-4.2$
Restore
psql teguhth < teguhth_singledb.sql
-bash-4.2$ psql teguhth < teguhth_singledb.sql
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
COPY 6
COPY 6
COPY 12
COPY 15
COPY 6
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
-bash-4.2$
sample log
postgres=# \c teguhth
You are now connected to database "teguhth" as user "postgres".
teguhth=# select * from pembelian;
ERROR: relation "pembelian" does not exist
LINE 1: select * from pembelian;
^
teguhth=# select * from pembelian;
kode_pembelian | kode_barang | kode_customer | tanggal_pembelian | jumlah_pembelian
----------------+-------------+---------------+-------------------+------------------
BEL-E001 | ELK-01 | J-0001 | 2002-05-20 | 3
BEL-E002 | ELK-01 | J-0001 | 2002-05-21 | 4
BEL-E003 | ELK-01 | J-0002 | 2002-05-20 | 2
BEL-E004 | ELK-01 | B-0001 | 2002-05-20 | 2
BEL-E005 | ELK-01 | B-0002 | 2002-05-22 | 3
BEL-E006 | ELK-02 | J-0001 | 2002-06-24 | 1
BEL-E007 | ELK-02 | J-0002 | 2002-06-24 | 1
BEL-E008 | ELK-02 | B-0001 | 2002-06-25 | 2
BEL-E009 | ELK-02 | B-0002 | 2002-06-25 | 2
BEL-E010 | ELK-03 | J-0001 | 2002-06-20 | 5
BEL-E011 | ELK-03 | J-0002 | 2002-07-02 | 4
BEL-E012 | ELK-03 | B-0001 | 2002-07-02 | 6
BEL-E013 | ELK-03 | J-0001 | 2002-07-10 | 5
BEL-E014 | ELK-04 | J-0002 | 2002-07-15 | 12
BEL-E015 | ELK-04 | B-0002 | 2002-07-17 | 15
(15 rows)
teguhth=#
teguhth=# select * from teguhth.public.pembelian;
kode_pembelian | kode_barang | kode_customer | tanggal_pembelian | jumlah_pembelian
----------------+-------------+---------------+-------------------+------------------
BEL-E001 | ELK-01 | J-0001 | 2002-05-20 | 3
BEL-E002 | ELK-01 | J-0001 | 2002-05-21 | 4
BEL-E003 | ELK-01 | J-0002 | 2002-05-20 | 2
BEL-E004 | ELK-01 | B-0001 | 2002-05-20 | 2
BEL-E005 | ELK-01 | B-0002 | 2002-05-22 | 3
BEL-E006 | ELK-02 | J-0001 | 2002-06-24 | 1
BEL-E007 | ELK-02 | J-0002 | 2002-06-24 | 1
BEL-E008 | ELK-02 | B-0001 | 2002-06-25 | 2
BEL-E009 | ELK-02 | B-0002 | 2002-06-25 | 2
BEL-E010 | ELK-03 | J-0001 | 2002-06-20 | 5
BEL-E011 | ELK-03 | J-0002 | 2002-07-02 | 4
BEL-E012 | ELK-03 | B-0001 | 2002-07-02 | 6
BEL-E013 | ELK-03 | J-0001 | 2002-07-10 | 5
BEL-E014 | ELK-04 | J-0002 | 2002-07-15 | 12
BEL-E015 | ELK-04 | B-0002 | 2002-07-17 | 15
(15 rows)
teguhth=#
2. Backup All Databases
Backup all databases in PostgreSQL using pg_dumpall utility.
pg_dumpall > alldbs.sql
Restore
psql < alldbs.sql
-bash-4.2$ pg_dumpall > alldbs.sql
-bash-4.2$
-bash-4.2$ ls
alldbs.sql teguhth_singledb.sql
-bash-4.2$ ls -l
total 20
-rw-r--r-- 1 postgres postgres 9213 Jul 25 13:14 alldbs.sql
-rw-r--r-- 1 postgres postgres 7023 Jul 25 13:12 teguhth_singledb.sql
-bash-4.2$
drop database teguhth
postgres=# select datname from pg_database;
datname
-----------
postgres
template1
template0
teguhth
(4 rows)
postgres=# drop database teguhth;
DROP DATABASE
postgres=# select datname from pg_database;
datname
-----------
postgres
template1
template0
(3 rows)
postgres=#
log
Restore: all database backup using following command.
-bash-4.2$ cd backups/
-bash-4.2$ ls
alldbs.sql teguhth_singledb.sql
Restore
psql < alldbs.sql
-bash-4.2$ psql < alldbs.sql
SET
SET
SET
ERROR: role "postgres" already exists
ALTER ROLE
You are now connected to database "template1" as user "postgres".
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
You are now connected to database "postgres" as user "postgres".
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
CREATE DATABASE
ALTER DATABASE
You are now connected to database "teguhth" as user "postgres".
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
COPY 6
COPY 6
COPY 12
COPY 15
COPY 6
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
-bash-4.2$
check restore
postgres=# select datname from pg_database;
datname
-----------
postgres
template1
template0
teguhth
(4 rows)
postgres=# \c teguhth
You are now connected to database "teguhth" as user "postgres".
teguhth=# select * from teguhth.public.pembelian;
kode_pembelian | kode_barang | kode_customer | tanggal_pembelian | jumlah_pembelian
----------------+-------------+---------------+-------------------+------------------
BEL-E001 | ELK-01 | J-0001 | 2002-05-20 | 3
BEL-E002 | ELK-01 | J-0001 | 2002-05-21 | 4
BEL-E003 | ELK-01 | J-0002 | 2002-05-20 | 2
BEL-E004 | ELK-01 | B-0001 | 2002-05-20 | 2
BEL-E005 | ELK-01 | B-0002 | 2002-05-22 | 3
BEL-E006 | ELK-02 | J-0001 | 2002-06-24 | 1
BEL-E007 | ELK-02 | J-0002 | 2002-06-24 | 1
BEL-E008 | ELK-02 | B-0001 | 2002-06-25 | 2
BEL-E009 | ELK-02 | B-0002 | 2002-06-25 | 2
BEL-E010 | ELK-03 | J-0001 | 2002-06-20 | 5
BEL-E011 | ELK-03 | J-0002 | 2002-07-02 | 4
BEL-E012 | ELK-03 | B-0001 | 2002-07-02 | 6
BEL-E013 | ELK-03 | J-0001 | 2002-07-10 | 5
BEL-E014 | ELK-04 | J-0002 | 2002-07-15 | 12
BEL-E015 | ELK-04 | B-0002 | 2002-07-17 | 15
(15 rows)
teguhth=#
3. Backup Single Table
pg_dump -d teguhth -t pembelian > teguhth_pembelian.sql
[root@teguhth ~]# su - postgres
Last login: Thu Aug 3 10:46:34 WIB 2023 on pts/0
-bash-4.2$ cd /var/lib/pgsql/14/backups/
-bash-4.2$ ls
alldbs.sql teguhth_singledb.sql
-bash-4.2$
-bash-4.2$ pg_dump -d teguhth -t pembelian > teguhth_pembelian.sql
-bash-4.2$ ls
alldbs.sql teguhth_pembelian.sql teguhth_singledb.sql
-bash-4.2$
drop table
teguhth=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | barang | table | postgres
public | customer | table | postgres
public | pasok | table | postgres
public | pembelian | table | postgres
public | suplier | table | postgres
(5 rows)
teguhth=# drop table pembelian;
DROP TABLE
teguhth=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | barang | table | postgres
public | customer | table | postgres
public | pasok | table | postgres
public | suplier | table | postgres
(4 rows)
teguhth=#
Restore: single table backup to database. Make sure your backup file contains only single table backup which you want to restore
restore table
psql teguhth < teguhth_pembelian.sql
-bash-4.2$ psql teguhth < teguhth_pembelian.sql
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 15
ALTER TABLE
ALTER TABLE
ALTER TABLE
-bash-4.2$
check
teguhth=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | barang | table | postgres
public | customer | table | postgres
public | pasok | table | postgres
public | pembelian | table | postgres
public | suplier | table | postgres
(5 rows)
teguhth=# drop table pembelian;
DROP TABLE
teguhth=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | barang | table | postgres
public | customer | table | postgres
public | pasok | table | postgres
public | suplier | table | postgres
(4 rows)
teguhth=#
teguhth=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | barang | table | postgres
public | customer | table | postgres
public | pasok | table | postgres
public | pembelian | table | postgres
public | suplier | table | postgres
(5 rows)
teguhth=#
4. Compressed Backup
pg_dump teguhth | gzip > gunzipteguhth.sql.gz
Backup
-bash-4.2$ pg_dump teguhth | gzip > gunzipteguhth.sql.gz
-bash-4.2$ ls
alldbs.sql gzteguhth_split.pql.gzaa teguhth_pembelian.sql teguhth_split.pqlaa teguhth_split.pqlac teguhth_split.pqlae teguhth_split.pqlag
gunzipteguhth.sql.gz gzteguhth_split.pql.gzab teguhth_singledb.sql teguhth_split.pqlab teguhth_split.pqlad teguhth_split.pqlaf
-bash-4.2$
Restore
gunzip -c gunzipteguhth.sql.gz | psql teguhth
-bash-4.2$ gunzip -c gunzipteguhth.sql.gz | psql teguhth
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
SET
SET
ERROR: relation "barang" already exists
ALTER TABLE
ERROR: relation "customer" already exists
ALTER TABLE
ERROR: relation "pasok" already exists
ALTER TABLE
CREATE TABLE
ALTER TABLE
ERROR: relation "suplier" already exists
ALTER TABLE
ERROR: duplicate key value violates unique constraint "barang_pkey"
DETAIL: Key (kode_barang)=(ELK-01) already exists.
CONTEXT: COPY barang, line 1
ERROR: duplicate key value violates unique constraint "customer_pkey"
DETAIL: Key (kode_customer)=(J-0001) already exists.
CONTEXT: COPY customer, line 1
ERROR: duplicate key value violates unique constraint "pasok_pkey"
DETAIL: Key (kode_pasok, kode_barang, kode_suplier)=(PAS-E001 , ELK-01, EJ-01) already exists.
CONTEXT: COPY pasok, line 1
COPY 15
ERROR: duplicate key value violates unique constraint "suplier_pkey"
DETAIL: Key (kode_suplier)=(EJ-01) already exists.
CONTEXT: COPY suplier, line 1
ERROR: multiple primary keys for table "barang" are not allowed
ERROR: multiple primary keys for table "customer" are not allowed
ERROR: multiple primary keys for table "pasok" are not allowed
ALTER TABLE
ERROR: multiple primary keys for table "suplier" are not allowed
ERROR: constraint "pasok_kode_barang_fkey" for relation "pasok" already exists
ERROR: constraint "pasok_kode_suplier_fkey" for relation "pasok" already exists
ALTER TABLE
ALTER TABLE
-bash-4.2$
5. Split Backup in Multiple Files and Restore
backup split
pg_dump teguhth | split -b 1k - teguhth_split.pql
-bash-4.2$ pwd
/var/lib/pgsql/14/backups
-bash-4.2$ ls
alldbs.sql teguhth_pembelian.sql teguhth_singledb.sql
-bash-4.2$
-bash-4.2$ pg_dump teguhth | split -b 1k - teguhth_split.pql
-bash-4.2$ ls
alldbs.sql teguhth_singledb.sql teguhth_split.pqlab teguhth_split.pqlad teguhth_split.pqlaf
teguhth_pembelian.sql teguhth_split.pqlaa teguhth_split.pqlac teguhth_split.pqlae teguhth_split.pqlag
-bash-4.2$
restore split
cat teguhth_split* | psql teguhth
postgres=# \c teguhth
You are now connected to database "teguhth" as user "postgres".
teguhth=# drop table pembelian;
DROP TABLE
teguhth=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | barang | table | postgres
public | customer | table | postgres
public | pasok | table | postgres
public | suplier | table | postgres
(4 rows)
teguhth=#
-bash-4.2$ cd 14/backups/
-bash-4.2$ ls
alldbs.sql teguhth_singledb.sql teguhth_split.pqlab teguhth_split.pqlad teguhth_split.pqlaf
teguhth_pembelian.sql teguhth_split.pqlaa teguhth_split.pqlac teguhth_split.pqlae teguhth_split.pqlag
-bash-4.2$
-bash-4.2$ cat teguhth_split* | psql teguhth
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
SET
SET
ERROR: relation "barang" already exists
ALTER TABLE
ERROR: relation "customer" already exists
ALTER TABLE
ERROR: relation "pasok" already exists
ALTER TABLE
CREATE TABLE
ALTER TABLE
ERROR: relation "suplier" already exists
ALTER TABLE
ERROR: duplicate key value violates unique constraint "barang_pkey"
DETAIL: Key (kode_barang)=(ELK-01) already exists.
CONTEXT: COPY barang, line 1
ERROR: duplicate key value violates unique constraint "customer_pkey"
DETAIL: Key (kode_customer)=(J-0001) already exists.
CONTEXT: COPY customer, line 1
ERROR: duplicate key value violates unique constraint "pasok_pkey"
DETAIL: Key (kode_pasok, kode_barang, kode_suplier)=(PAS-E001 , ELK-01, EJ-01) already exists.
CONTEXT: COPY pasok, line 1
COPY 15
ERROR: duplicate key value violates unique constraint "suplier_pkey"
DETAIL: Key (kode_suplier)=(EJ-01) already exists.
CONTEXT: COPY suplier, line 1
ERROR: multiple primary keys for table "barang" are not allowed
ERROR: multiple primary keys for table "customer" are not allowed
ERROR: multiple primary keys for table "pasok" are not allowed
ALTER TABLE
ERROR: multiple primary keys for table "suplier" are not allowed
ERROR: constraint "pasok_kode_barang_fkey" for relation "pasok" already exists
ERROR: constraint "pasok_kode_suplier_fkey" for relation "pasok" already exists
ALTER TABLE
ALTER TABLE
-bash-4.2$
postgres=# \c teguhth
You are now connected to database "teguhth" as user "postgres".
teguhth=# drop table pembelian;
DROP TABLE
teguhth=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | barang | table | postgres
public | customer | table | postgres
public | pasok | table | postgres
public | suplier | table | postgres
(4 rows)
teguhth=#
teguhth=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | barang | table | postgres
public | customer | table | postgres
public | pasok | table | postgres
public | pembelian | table | postgres
public | suplier | table | postgres
(5 rows)
teguhth=#
teguhth=#
6. Split Backup in Multiple Files and Restore using compresed
backup split using gz
pg_dump teguhth | gzip | | split -b 1k - teguhth_split.pql.gz
-bash-4.2$ pg_dump teguhth | gzip | split -b 1k - gzteguhth_split.pql.gz
-bash-4.2$
-bash-4.2$ ls
alldbs.sql gzteguhth_split.pql.gzab teguhth_singledb.sql teguhth_split.pqlab teguhth_split.pqlad teguhth_split.pqlaf
gzteguhth_split.pql.gzaa teguhth_pembelian.sql teguhth_split.pqlaa teguhth_split.pqlac teguhth_split.pqlae teguhth_split.pqlag
-bash-4.2$
restore
cat gzteguhth_split.pql.gz* | psql teguhth
teguhth-#
teguhth=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | barang | table | postgres
public | customer | table | postgres
public | pasok | table | postgres
public | pembelian | table | postgres
public | suplier | table | postgres
(5 rows)
teguhth=# drop table pembelian;
DROP TABLE
teguhth=#
teguhth=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | barang | table | postgres
public | customer | table | postgres
public | pasok | table | postgres
public | suplier | table | postgres
(4 rows)
teguhth=#
restore
-bash-4.2$ cat gzteguhth_split.pql.gz* | psql teguhth
invalid command \▒▒▒U▒▒▒▒٦▒▒▒▒d▒▒▒n뼤▒▒j}▒=▒{&ۍ▒}▒▒>▒▒|$▒s*s▒u:▒▒(▒▒▒њ▒▒'▒▒▒▒▒?J▒▒▒▒p'/▒▒▒▒▒ծ▒Y▒I▒3▒▒▒▒#▒ҧ▒)▒r!▒▒▒ړJ▒Ȗ▒K▒▒▒@▒▒▒4▒▒▒E▒▒N▒/0▒6T▒▒0ƪ▒
ERROR: invalid byte sequence for encoding "UTF8": 0x8b
-bash-4.2$ cat gzteguhth_split.pql.gz* | psql teguhth
invalid command \▒▒▒U▒▒▒▒٦▒▒▒▒d▒▒▒n뼤▒▒j}▒=▒{&ۍ▒}▒▒>▒▒|$▒s*s▒u:▒▒(▒▒▒њ▒▒'▒▒▒▒▒?J▒▒▒▒p'/▒▒▒▒▒ծ▒Y▒I▒3▒▒▒▒#▒ҧ▒)▒r!▒▒▒ړJ▒Ȗ▒K▒▒▒@▒▒▒4▒▒▒E▒▒N▒/0▒6T▒▒0ƪ▒
ERROR: invalid byte sequence for encoding "UTF8": 0x8b
-bash-4.2$ PuTTY
-bash: PuTTY: command not found
-bash-4.2$
https://tecadmin.net/backup-and-restore-database-in-postgresql/
No comments:
Post a Comment