PostgreSQL dump & restore

• 개요

PostgreSQL dump & restore 관련 명령어

 

• dump & restore

# sudo dnf install -y https://download.postgresql.org/pub/repos/yum/14/redhat/rhel-8-x86_64/postgresql14-14.13-1PGDG.rhel8.x86_64.rpm

# psql -h <host> -p 5432 -U user -d <database>
Password for user user: 
psql (14.13, server 13.15)
Type "help" for help.

database=> \l
                                        List of databases
      Name       |  Owner   | Encoding | Collate | Ctype |           Access privileges            
-----------------+----------+----------+---------+-------+----------------------------------------
 postgres        | postgres | UTF8     | C       | C     | postgres=CTc/postgres                 +
                 |          |          |         |       | pgautofailover_monitor=CTc/postgres   +
                 |          |          |         |       | pgautofailover_replicator=CTc/postgres
 template0       | postgres | UTF8     | C       | C     | =c/postgres                           +
                 |          |          |         |       | postgres=CTc/postgres
 template1       | postgres | UTF8     | C       | C     | =c/postgres                           +
                 |          |          |         |       | postgres=CTc/postgres
(6 rows)

database=> \c database
psql (14.13, server 13.15)
You are now connected to database "database" as user "user".
study_event_log=> CREATE SCHEMA common;
study_event_log=> \dn
  List of schemas
  Name  |  Owner   
--------+----------
 common | user
 public | postgres
(2 rows)


database=> \c database
psql (14.13, server 13.15)
You are now connected to database "database" as user "user".
database=> CREATE SCHEMA common;
database=> \dn
  List of schemas
  Name  |  Owner   
--------+----------
 common | user
 public | postgres
(2 rows)


database=> \c database
psql (14.13, server 13.15)
You are now connected to database "database" as user "user".
data_dashboard=> CREATE SCHEMA common;
sa_data_mart=> \dn
  List of schemas
  Name  |  Owner   
--------+----------
 common | user
 public | postgres
(2 rows)

# pg_dump -h <host> -U user -d database -n common \
  -t common.table1 \
  -t common.table2 \
  -t common.table3 \
  --clean --if-exists > postgresql_selected_tables.sql

# psql -h <host> -U user -d database -f postgresql_selected_tables.sql

# psql -h <host> -U user -d database
SELECT * FROM common.table1 LIMIT 5;
SELECT * FROM common.table2 LIMIT 5;
SELECT * FROM common.table3 LIMIT 5;"