※ 목차 ※
Major Upgrade Test Data
Major Upgrade Using pg_dumpall
Major Upgrade Using pg_dump
Major Upgrade Using pg_upgrade
Major Upgrade Using pg_upgrade Link Option
Major Upgrade Using pg_upgradecluster(Only Debian계열)
PostgreSQL의 신규 기능들을 사용하기 위해서, 혹은 EOL 등의 이유로 PostgreSQL 업그레이드를 고려할 수 있습니다. Major버전 업그레이드의 경우 시스템 테이블과 Data 파일의 내부 아키텍처의 변화에 따라 버전 간의 호환성이 유지되지 않기 때문에 pg_dump, pg_restore, pg_upgrade 명령어 등을 통해 버전 업그레이드를 수행해야 합니다. 업그레이드 작업 이외에도 Configuration 설정과 VACUUM과 같은 부가적인 작업이 필요할 수 있습니다. 이러한 작업은 Minor버전 업그레이드보다 더 많은 작업시간과 서비스 중지 시간이 필요할 수 있습니다.
본 문서는 DDL, DML 등의 작업이 없다는 가정하에 동일 서버 대상으로 PostgreSQL 9.6에서 PostgreSQL 12로 Major버전을 업그레이드하는 여러 방법을 설명합니다. 마지막에 설명하는 pg_upgradecluster는 Debian계열(Ubuntu)에서만 사용 가능하며, PostgreSQL의 응용프로그램을 사용하는 업그레이드 방법들은 OS Platform에 상관없이 사용할 수 있습니다. PostgreSQL 12 설치 및 설정→Dump→Restore→Vacuum→업그레이드 확인 순으로 기술됩니다.
📢 응용프로그램에 대한 설명은 [PostgreSQL Setup - Version & Utility]에서 확인.
PostgreSQL 12 설치에 대한 내용은 [PostgreSQL Setup - Installation]에서 확인.
| Major Upgrade Test Data | 목차 |
업그레이드 방법을 설명하기에 앞서 업그레이드 대상인 PostgreSQL 9.6에 대한 Configuration File 설정 및 Test Data를 생성합니다. 테스트에 사용되는 업그레이드 대상 서버는 CentOS 7.9에 PostgreSQL 9.6.24 버전입니다.
postgresql.conf 설정
## postgresql.conf 변경 내용
listen_addresses = '*'
port = 5432
max_connections = 300
shared_buffers = 4GB
maintenance_work_mem = 64MB
autovacuum = off
track_counts = off
work_mem = 64MB
effective_cache_size = 1GB
wal_buffers = -1
shared_preload_libraries = 'pg_stat_statements'
pg_hba.conf 설정
## pg_hba.conf 변경 내용
host all all 0.0.0.0/0 trust
Test Data
| Database | Table Name | Row Count | Size | Extension |
| exemdb_01 | exemtab_01 | 5,000 만건 | 약 6GB | pg_stat_statements |
| exemdb_02 | exemtab_02 | 100 만건 | 약 125MB | pg_visibility |
더보기
-- Database 생성
CREATE DATABASE exemdb_01 ;
CREATE DATABASE exemdb_02 ;
------------------------------------------------------
-- ## exemdb_01 Database 데이터 입력 (5,000만건 약 6GB)
\c exemdb_01
-- ## 테이블 및 데이터 생성
CREATE TABLE exemtab_01 AS
SELECT gs_data AS idx ,
'exemdb_01 TEST DATA : ' || gs_data AS string_data ,
md5( RANDOM()::text ) AS random_data
FROM GENERATE_SERIES( 1, 50000000 ) AS gs_data ;
-- ## 인덱스 생성
CREATE INDEX exemtab_01_ix01 on exemtab_01 ( idx ) ;
-- ## Extension 생성
CREATE EXTENSION pg_stat_statements ;
------------------------------------------------------
-- ## exemdb_02 Database 데이터 입력 (100만건 약 125MB)
\c exemdb_02
-- ## 테이블 및 데이터 생성
CREATE TABLE exemtab_02 AS
SELECT gs_data AS idx ,
'exemdb_02 TEST DATA : ' || gs_data AS string_data ,
md5( RANDOM()::text ) AS random_data
FROM GENERATE_SERIES( 1, 1000000 ) AS gs_data ;
-- ## 인덱스 생성
CREATE INDEX exemtab_02_ix01 on exemtab_02 ( idx ) ;
-- ## Extension 생성
CREATE EXTENSION pg_visibility ;더보기
## Database Size
-bash-4.2$ psql -c "\l+"
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
exemdb_01 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 5901 MB | pg_default |
exemdb_02 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 125 MB | pg_default |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7343 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7233 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7233 kB | pg_default | default templatefor new databases
| | | | | postgres=CTc/postgres | | |
(5 rows)
| Major Upgrade Using pg_dumpall | 목차 |
pg_dumpall은 PostgreSQL의 전체 Database Cluster에 대한 Backup을 수행하는 응용프로그램입니다. pg_dumpall에는 Role, Tablespace 및 Database 등등 많은 내용이 포함됩니다.
pg_dumpall은 사용자가 pg_dump를 수행하지 않을 뿐이며, 내부적으로 pg_dump 수행됨에 따라서PostgreSQL 전체 Database Cluster의 Backup을 수행하려면 서버에 충분한 디스크 공간이 필요합니다. 업그레이드가 동일한 서버 내에서 수행되는 경우 기존 Database Cluster 사이즈보다 큰 디스크 공간이 있는지 먼저 확인해야 합니다. 새로운 PostgreSQL Database Cluster 전용 공간, Dump File 공간 등이 필요합니다.
1. PostgreSQL 12 설치 및 설정
PostgreSQL 12 설치
📢 PostgreSQL Install 내용은 [PostgreSQL Setup - Installation] 확인.
PostgreSQL 12 - postgresql.conf 설정
Configuration은 버전별로 차이가 있기 때문에 완벽하게 호환되지 않을 수 있으며, PostgreSQL 12 설치 시 postgresql.conf 내용은 기본값으로 설정되어 있기 때문에 PostgreSQL 9.6과 비교하여 PostgreSQL 12에 필요한 postgresql.conf 내용을 변경해야 합니다. 기존에 설치되어 있는 PostgreSQL 9.6과 Port 충돌을 피하기 위해 PostgreSQL 12에서 사용하는 Port도 변경합니다.
## [postgres] PostgreSQL 12 Configuration 변경
echo "port = 5433" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "listen_addresses = '*'" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "max_connections = 300" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "shared_buffers = 4GB" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "maintenance_work_mem = 64MB" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "autovacuum = off" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "track_counts = off" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "work_mem = 64MB" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "effective_cache_size = 1GB" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "wal_buffers = -1" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "shared_preload_libraries = 'pg_stat_statements'" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "host all all 0.0.0.0/0 trust" >> /home/postgres/PostgreSQL12_Data/pg_hba.conf
## [postgres] PostgreSQL 12 기동
/usr/pgsql-12/bin/pg_ctl -D /home/postgres/PostgreSQL12_Data start
2. PostgreSQL 9.6 Cluster Dump
PostgreSQL 12의 pg_dumpall 응용프로그램으로 PostgreSQL 9.6 Cluster Dump를 생성합니다. PostgreSQL 9.6 Cluster의 크기가 큰 경우 pg_dumpall 수행이 오래 걸릴 수 있습니다.
📢 pg_dumpall의 사용법 및 설명서는 [PostgreSQL Setup - Version & Utility] 확인.
## [postgres] Dump Directory 생성
mkdir /home/postgres/dumpdir
## [postgres] PostgreSQL 9.6 Cluster Dump
## PostgreSQL 9.6 사용 Port는 5432로 "-p 5432" 또는 "--port=5432" 옵션을 명시하거나, 생략가능
/usr/pgsql-12/bin/pg_dumpall --port=5432 -f /home/postgres/dumpdir/dumpall.sql더보기
-bash-4.2$ mkdir /home/postgres/dumpdir
-bash-4.2$ /usr/pgsql-12/bin/pg_dumpall --port=5432 -v -f /home/postgres/dumpdir/dumpall.sql
-bash-4.2$ ls -l /home/postgres/dumpdir
total 4194240
-rw-r--r--. 1 postgres postgres 3696561059 Sep 14 11:09 dumpall.sql더보기
-bash-4.2$ /usr/pgsql-12/bin/pg_dumpall --port=5432 -v -f /home/postgres/dumpdir/dumpall.sql
pg_dumpall: executing SELECT pg_catalog.set_config('search_path', '', false);
pg_dumpall: executing SELECT oid, rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, rolreplication, rolbypassrls, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, rolname = current_user AS is_current_user FROM pg_authid WHERE rolname !~ '^pg_' ORDER BY 2
pg_dumpall: executing SELECT provider, label FROM pg_catalog.pg_shseclabel WHERE classoid = 'pg_catalog.pg_authid'::pg_catalog.regclass AND objoid = '10'
pg_dumpall: executing SELECT setconfig[1] FROM pg_db_role_setting WHERE setdatabase = 0 AND setrole = (SELECT oid FROM pg_authid WHERE rolname = 'postgres')
pg_dumpall: executing SELECT ur.rolname AS roleid, um.rolname AS member, a.admin_option, ug.rolname AS grantor FROM pg_auth_members a LEFT JOIN pg_authid ur on ur.oid = a.roleid LEFT JOIN pg_authid um on um.oid = a.member LEFT JOIN pg_authid ug on ug.oid = a.grantor WHERE NOT (ur.rolname ~ '^pg_' AND um.rolname ~ '^pg_')ORDER BY 1,2,3
pg_dumpall: executing SELECT oid, spcname, pg_catalog.pg_get_userbyid(spcowner) AS spcowner, pg_catalog.pg_tablespace_location(oid), (SELECT array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM unnest(coalesce(spcacl,acldefault('t',spcowner))) WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM unnest(acldefault('t',spcowner)) AS init(init_acl) WHERE acl = init_acl)) AS spcacls) AS spcacl, (SELECT array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM unnest(acldefault('t',spcowner)) WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM unnest(coalesce(spcacl,acldefault('t',spcowner))) AS permp(orig_acl) WHERE acl = orig_acl)) AS rspcacls) AS rspcacl, array_to_string(spcoptions, ', '),pg_catalog.shobj_description(oid, 'pg_tablespace') FROM pg_catalog.pg_tablespace WHERE spcname !~ '^pg_' ORDER BY 1
pg_dumpall: executing SELECT datname FROM pg_database d WHERE datallowconn ORDER BY (datname <> 'template1'), datname
pg_dumpall: dumping database "template1"
pg_dumpall: running ""/usr/pgsql-12/bin/pg_dump" -f /home/postgres/dumpdir/dumpall.sql -v -Fa ' dbname=template1'"
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: flagging inherited columns in subtables
pg_dump: reading indexes
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-level security policies
pg_dump: reading publications
pg_dump: reading publication membership
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: implied data-only restore
pg_dumpall: dumping database "exemdb_01"
pg_dumpall: running ""/usr/pgsql-12/bin/pg_dump" -f /home/postgres/dumpdir/dumpall.sql -v --create -Fa ' dbname=exemdb_01'"
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 "public.exemtab_01"
pg_dump: finding the columns and types of table "public.pg_stat_statements"
pg_dump: flagging inherited columns in subtables
pg_dump: reading indexes
pg_dump: reading indexes for table "public.exemtab_01"
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-level security policies
pg_dump: reading publications
pg_dump: reading publication membership
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: creating DATABASE "exemdb_01"
pg_dump: connecting to new database "exemdb_01"
pg_dump: creating EXTENSION "pg_stat_statements"
pg_dump: creating COMMENT "EXTENSION pg_stat_statements"
pg_dump: creating TABLE "public.exemtab_01"
pg_dump: processing data for table "public.exemtab_01"
pg_dump: dumping contents of table "public.exemtab_01"
pg_dump: creating INDEX "public.exemtab_01_ix01"
pg_dumpall: dumping database "exemdb_02"
pg_dumpall: running ""/usr/pgsql-12/bin/pg_dump" -f /home/postgres/dumpdir/dumpall.sql -v --create -Fa ' dbname=exemdb_02'"
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 "public.exemtab_02"
pg_dump: flagging inherited columns in subtables
pg_dump: reading indexes
pg_dump: reading indexes for table "public.exemtab_02"
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-level security policies
pg_dump: reading publications
pg_dump: reading publication membership
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: creating DATABASE "exemdb_02"
pg_dump: connecting to new database "exemdb_02"
pg_dump: creating EXTENSION "pg_visibility"
pg_dump: creating COMMENT "EXTENSION pg_visibility"
pg_dump: creating TABLE "public.exemtab_02"
pg_dump: processing data for table "public.exemtab_02"
pg_dump: dumping contents of table "public.exemtab_02"
pg_dump: creating INDEX "public.exemtab_02_ix01"
pg_dumpall: dumping database "postgres"
pg_dumpall: running ""/usr/pgsql-12/bin/pg_dump" -f /home/postgres/dumpdir/dumpall.sql -v -Fa ' dbname=postgres'"
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: flagging inherited columns in subtables
pg_dump: reading indexes
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-level security policies
pg_dump: reading publications
pg_dump: reading publication membership
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: implied data-only restore
3. PostgreSQL 12 Restore
앞에서 생성된 PostgreSQL 9.6 Cluster Dump은 Text Format으로 psql을 통해 PostgreSQL 12에 Restore 합니다.
## [postgres] PostgreSQL 12 Restore
## PostgreSQL 12는 5433 Port을 사용하고 있어, -p 5433 옵션 추가
/usr/pgsql-12/bin/psql -p 5433 -f /home/postgres/dumpdir/dumpall.sql더보기
-bash-4.2$ /usr/pgsql-12/bin/psql -p 5433 -f /home/postgres/dumpdir/dumpall.sql
SET
SET
SET
psql:/home/postgres/dumpdir/dumpall.sql:14: 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
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
CREATE DATABASE
ALTER DATABASE
You are now connected to database "exemdb_01" as user "postgres".
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
SET
CREATE TABLE
ALTER TABLE
COPY 50000000
CREATE INDEX
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
CREATE DATABASE
ALTER DATABASE
You are now connected to database "exemdb_02" as user "postgres".
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
SET
CREATE TABLE
ALTER TABLE
COPY 1000000
CREATE INDEX
You are now connected to database "postgres" as user "postgres".
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
4. PostgreSQL 12 Analyze 실행
모든 데이터베이스 통계가 업데이트되도록 전체 Cluster 단위 ANALYZE를 실행합니다.
/usr/pgsql-12/bin/vacuumdb --port=5433 --all --analyze --jobs=4더보기
-bash-4.2$ /usr/pgsql-12/bin/vacuumdb --port=5433 --all --analyze --jobs=4
vacuumdb: vacuuming database "exemdb_01"
vacuumdb: vacuuming database "exemdb_02"
vacuumdb: vacuuming database "postgres"
vacuumdb: vacuuming database "template1"
5. PostgreSQL 12 Upgrade 확인
PostgreSQL Major Upgrade 확인
## [postgres] PostgreSQL 버전과 데이터베이스 리스트 확인
/usr/pgsql-12/bin/psql -p 5433
SELECT VERSION() ;
\l+더보기
-bash-4.2$ /usr/pgsql-12/bin/psql -p 5433
psql (12.12)
Type "help" for help.
postgres=# SELECT VERSION() ;
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 12.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+-------------------------------------------
exemdb_01 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 5903 MB | pg_default |
exemdb_02 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 126 MB | pg_default |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8329 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8049 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8177 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(5 rows)
exemdb_01 데이터베이스 데이터 확인
-- exemdb_01 테이블 Row : 5,000만건 / PG_STAT_STATEMENTS EXTENSION 확인
\c exemdb_01
SELECT COUNT(*) FROM exemtab_01 ;
\dx더보기
postgres=# \c exemdb_01
You are now connected to database "exemdb_01" as user "postgres".
exemdb_01=# SELECT COUNT(*) FROM exemtab_01 ;
count
----------
50000000
(1 row)
exemdb_01=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+-----------------------------------------------------------
pg_stat_statements | 1.7 | public | track execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
exemdb_02 데이터베이스 데이터 확인
-- exemdb_02 테이블 Row : 100만건 / PG_VISIBILITY EXTENSION 확인
\c exemdb_02
SELECT COUNT(*) FROM exemtab_02 ;
\dx더보기
exemdb_01=# \c exemdb_02
You are now connected to database "exemdb_02" as user "postgres".
exemdb_02=# SELECT COUNT(*) FROM exemtab_02 ;
count
---------
1000000
(1 row)
exemdb_02=# \dx
List of installed extensions
Name | Version | Schema | Description
---------------+---------+------------+----------------------------------------------------------------
pg_visibility | 1.2 | public | examine the visibility map (VM) and page-level visibility info
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
| Major Upgrade Using pg_dump | 목차 |
이번 단락에서는 pg_dump와 psql 응용프로그램을 통한 PostgreSQL 9.6에서 PostgreSQL 12로 업그레이드에 대한 내용을 기술합니다.
pg_dumpall을 통한 업그레이드와 마찬가지로 Database Cluster의 Backup을 수행하려면 서버에 충분한 디스크 공간이 필요합니다. 업그레이드가 동일한 서버 내에서 수행되는 경우 기존 Database Cluster 사이즈보다 큰 디스크 공간이 있는지 먼저 확인해야 합니다. 새로운 PostgreSQL Database Cluster 전용 공간, Dump File 공간 등이 필요합니다.
1. PostgreSQL 12 설치 및 설정
PostgreSQL 12 설치
📢 PostgreSQL Install 내용은 [PostgreSQL Setup - Installation] 확인.
PostgreSQL 12 - postgresql.conf 설정
Configuration은 버전별로 차이가 있기 때문에 완벽하게 호환되지 않을 수 있으며, PostgreSQL 12 설치 시 postgresql.conf 내용은 기본값으로 설정되어 있기 때문에 PostgreSQL 9.6과 비교하여 PostgreSQL 12에 필요한 postgresql.conf 내용을 변경해야 합니다. 기존에 설치되어 있는 PostgreSQL 9.6과 Port 충돌을 피하기 위해 PostgreSQL 12에서 사용하는 Port도 변경합니다.
## [postgres] PostgreSQL 12 Configuration 변경
echo "port = 5433" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "listen_addresses = '*'" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "max_connections = 300" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "shared_buffers = 4GB" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "maintenance_work_mem = 64MB" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "autovacuum = off" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "track_counts = off" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "work_mem = 64MB" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "effective_cache_size = 1GB" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "wal_buffers = -1" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "shared_preload_libraries = 'pg_stat_statements'" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "host all all 0.0.0.0/0 trust" >> /home/postgres/PostgreSQL12_Data/pg_hba.conf
## [postgres] PostgreSQL 12 기동
/usr/pgsql-12/bin/pg_ctl -D /home/postgres/PostgreSQL12_Data start
2. PostgreSQL 9.6 Cluster Global Object Dump & Restore
PostgreSQL 12의 pg_dumpall Utility를 이용하여 PostgreSQL 9.6 Cluster Global Object Dump를 생성합니다. 데이터 Dump가 아닌 Cluster 구조만 Dump 하기 때문에 오래 걸리지 않습니다. 사용자가 추가한 Role과 Tablespace가 없다면, 이번 단계를 수행하지 않아도 됩니다.
## [postgres] Dump Directory 생성
mkdir /home/postgres/dumpdir
## [postgres] PostgreSQL 9.6의 Global Object Dump 생성
## PostgreSQL 9.6 사용 Port는 5432로 "-p 5432" 또는 "--port=5432" 옵션을 명시하거나, 생략가능
/usr/pgsql-12/bin/pg_dumpall --port=5432 --globals-only -f /home/postgres/dumpdir/only_global.sql
## [postgres] PostgreSQL 12에 Global Object Restore
/usr/pgsql-12/bin/psql --port=5433 -f /home/postgres/dumpdir/only_global.sql더보기
-bash-4.2$ /usr/pgsql-12/bin/pg_dumpall --port=5432 -v --globals-only -f /home/postgres/dumpdir/only_global.sql
pg_dumpall: executing SELECT pg_catalog.set_config('search_path', '', false);
pg_dumpall: executing SELECT oid, rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, rolreplication, rolbypassrls, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, rolname = current_user AS is_current_user FROM pg_authid WHERE rolname !~ '^pg_' ORDER BY 2
pg_dumpall: executing SELECT provider, label FROM pg_catalog.pg_shseclabel WHERE classoid = 'pg_catalog.pg_authid'::pg_catalog.regclass AND objoid = '10'
pg_dumpall: executing SELECT setconfig[1] FROM pg_db_role_setting WHERE setdatabase = 0 AND setrole = (SELECT oid FROM pg_authid WHERE rolname = 'postgres')
pg_dumpall: executing SELECT ur.rolname AS roleid, um.rolname AS member, a.admin_option, ug.rolname AS grantor FROM pg_auth_members a LEFT JOIN pg_authid ur on ur.oid = a.roleid LEFT JOIN pg_authid um on um.oid = a.member LEFT JOIN pg_authid ug on ug.oid = a.grantor WHERE NOT (ur.rolname ~ '^pg_' AND um.rolname ~ '^pg_')ORDER BY 1,2,3
pg_dumpall: executing SELECT oid, spcname, pg_catalog.pg_get_userbyid(spcowner) AS spcowner, pg_catalog.pg_tablespace_location(oid), (SELECT array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM unnest(coalesce(spcacl,acldefault('t',spcowner))) WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM unnest(acldefault('t',spcowner)) AS init(init_acl) WHERE acl = init_acl)) AS spcacls) AS spcacl, (SELECT array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM unnest(acldefault('t',spcowner)) WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM unnest(coalesce(spcacl,acldefault('t',spcowner))) AS permp(orig_acl) WHERE acl = orig_acl)) AS rspcacls) AS rspcacl, array_to_string(spcoptions, ', '),pg_catalog.shobj_description(oid, 'pg_tablespace') FROM pg_catalog.pg_tablespace WHERE spcname !~ '^pg_' ORDER BY 1
-bash-4.2$ ls -l /home/postgres/dumpdir
-rw-r--r--. 1 postgres postgres 422 Sep 14 16:14 only_global.sql
3-1. PostgreSQL 9.6 Cluster Dump & Restore
PostgreSQL 12의 pg_dump 응용프로그램으로 PostgreSQL 9.6 Cluster Dump를 생성합니다. pg_dump 특성상 하나의 Database에 대해서만 Dump가 가능하여 Database 개수만큼 pg_dump를 수행해야 합니다.
📢 pg_dump의 사용법 및 설명서는 [PostgreSQL Setup - Version & Utility] 확인.
## [postgres] Dump Directory 생성
mkdir /home/postgres/dumpdir
## PostgreSQL 9.6 사용 Port는 5432로 "-p 5432" 또는 "--port=5432" 옵션을 명시하거나, 생략가능
## [postgres] exemdb_01 Database Dump (-C 옵션으로 Database 생성문까지 포함)
/usr/pgsql-12/bin/pg_dump -p 5432 -C -d exemdb_01 -f /home/postgres/dumpdir/exemdb_01_dump.sql
## [postgres] exemdb_02 Database Dump (-C 옵션으로 Database 생성문까지 포함)
/usr/pgsql-12/bin/pg_dump -p 5432 -C -d exemdb_02 -f /home/postgres/dumpdir/exemdb_02_dump.sql더보기
-bash-4.2$ mkdir /home/postgres/dumpdir
-bash-4.2$ /usr/pgsql-12/bin/pg_dump -p 5432 -C -d exemdb_01 -f /home/postgres/dumpdir/exemdb_01_dump.sql
-bash-4.2$ /usr/pgsql-12/bin/pg_dump -p 5432 -C -d exemdb_02 -f /home/postgres/dumpdir/exemdb_02_dump.sql
-bash-4.2$ ls -l /home/postgres/dumpdir
total 3609928
-rw-r--r--. 1 postgres postgres 3627780126 Sep 14 14:59 exemdb_01_dump.sql
-rw-r--r--. 1 postgres postgres 68780109 Sep 14 15:00 exemdb_02_dump.sql더보기
-bash-4.2$ /usr/pgsql-12/bin/pg_dump -p 5432 -v -C -d exemdb_01 -f /home/postgres/dumpdir/exemdb_01_dump.sql
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 "public.exemtab_01"
pg_dump: finding the columns and types of table "public.pg_stat_statements"
pg_dump: flagging inherited columns in subtables
pg_dump: reading indexes
pg_dump: reading indexes for table "public.exemtab_01"
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-level security policies
pg_dump: reading publications
pg_dump: reading publication membership
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: creating DATABASE "exemdb_01"
pg_dump: connecting to new database "exemdb_01"
pg_dump: creating EXTENSION "pg_stat_statements"
pg_dump: creating COMMENT "EXTENSION pg_stat_statements"
pg_dump: creating TABLE "public.exemtab_01"
pg_dump: processing data for table "public.exemtab_01"
pg_dump: dumping contents of table "public.exemtab_01"
pg_dump: creating INDEX "public.exemtab_01_ix01"
앞에서 생성된 PostgreSQL 9.6 Cluster Dump는 Text Format으로 psql을 통해 PostgreSQL 12에 Restore 합니다.
## PostgreSQL 12는 5433 Port을 사용하고 있어, -p 5433 옵션 추가
## [postgres] exemdb_01 Database Restore
/usr/pgsql-12/bin/psql -p 5433 -f /home/postgres/dumpdir/exemdb_01_dump.sql
## [postgres] exemdb_02 Database Restore
/usr/pgsql-12/bin/psql -p 5433 -f /home/postgres/dumpdir/exemdb_02_dump.sql더보기
-bash-4.2$ /usr/pgsql-12/bin/psql -p 5433 -f /home/postgres/dumpdir/exemdb_01_dump.sql
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
CREATE DATABASE
ALTER DATABASE
You are now connected to database "exemdb_01" as user "postgres".
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
SET
CREATE TABLE
ALTER TABLE
COPY 50000000
CREATE INDEX
real 2m34.369s
user 0m6.551s
sys 0m3.201s
-bash-4.2$ time /usr/pgsql-12/bin/psql -p 5433 -f /home/postgres/dumpdir/exemdb_02_dump.sql
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
CREATE DATABASE
ALTER DATABASE
You are now connected to database "exemdb_02" as user "postgres".
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
SET
CREATE TABLE
ALTER TABLE
COPY 1000000
CREATE INDEX
3-2. PostgreSQL 9.6 Cluster Dump & Restore(-F 옵션)
DDatabase의 크기가 클수록 Dump에 많은 시간이 소요됩니다. Dump를 Parallel로 수행하여 소요시간을 단축시킬 수 있습니다. 단, Parallel Dump 수행은 Directory Format(pg_dump -Fd 옵션)에서만 가능하며, pg_restore 응용프로그램을 사용하여 Restore 해야 합니다.
## [postgres] Dump Directory 생성
mkdir /home/postgres/dumpdir/exemdb_01_dir
mkdir /home/postgres/dumpdir/exemdb_02_dir
## PostgreSQL 9.6 사용 Port는 5432로 "-p 5432" 또는 "--port=5432" 옵션을 명시하거나, 생략가능
## -C 옵션으로 Database 생성문까지 포함
## -Fd 옵션으로 Directory Format으로 Dump 생성
## -j 옵션으로 Parallel 수행(Degree:4)
## [postgres] exemdb_01 Database Dump
/usr/pgsql-12/bin/pg_dump -p 5432 -C -Fd -j 4 -d exemdb_01 -f /home/postgres/dumpdir/exemdb_01_dir
## [postgres] exemdb_02 Database Dump
/usr/pgsql-12/bin/pg_dump -p 5432 -C -Fd -j 4 -d exemdb_02 -f /home/postgres/dumpdir/exemdb_02_dir더보기
-bash-4.2$ mkdir /home/postgres/dumpdir/exemdb_01_dir
-bash-4.2$ mkdir /home/postgres/dumpdir/exemdb_02_dir
-bash-4.2$ /usr/pgsql-12/bin/pg_dump -p 5432 -C -Fd -j 4 -d exemdb_01 -f /home/postgres/dumpdir/exemdb_01_dir
-bash-4.2$ /usr/pgsql-12/bin/pg_dump -p 5432 -C -Fd -j 4 -d exemdb_02 -f /home/postgres/dumpdir/exemdb_02_dir
-bash-4.2$ ls -l /home/postgres/dumpdir/exemdb_01_dir
-rw-r--r--. 1 postgres postgres 1250160019 Sep 14 16:30 3048.dat.gz
-rw-r--r--. 1 postgres postgres 2001 Sep 14 16:28 toc.dat
-bash-4.2$ ls -l /home/postgres/dumpdir/exemdb_02_dir
-rw-r--r--. 1 postgres postgres 24953385 Sep 14 16:31 3049.dat.gz
-rw-r--r--. 1 postgres postgres 1981 Sep 14 16:31 toc.dat더보기
-bash-4.2$ /usr/pgsql-12/bin/pg_dump -p 5432 -v -C -Fd -j 4 -d exemdb_01 -f /home/postgres/dumpdir/exemdb_01_dir
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 "public.exemtab_01"
pg_dump: finding the columns and types of table "public.pg_stat_statements"
pg_dump: flagging inherited columns in subtables
pg_dump: reading indexes
pg_dump: reading indexes for table "public.exemtab_01"
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-level security policies
pg_dump: reading publications
pg_dump: reading publication membership
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.exemtab_01"
pg_dump: finished item 3048 TABLE DATA exemtab_01
앞에서 생성된 PostgreSQL 9.6 Cluster Dump는 Directory Format으로 pg_restore를 통해 PostgreSQL 12에 Restore 합니다.
📢 pg_restore의 사용법 및 설명서는 [PostgreSQL Setup - Version & Utility] 확인.
## PostgreSQL 12는 5433 Port을 사용하고 있어, -p 5433 옵션 추가
## -Fd 옵션으로 Directory Format Dump 사용
## -j 옵션으로 Parallel 수행(Degree:4)
## -C 옵션으로 Database 생성
## [postgres] exemdb_01 Database Restore
/usr/pgsql-12/bin/pg_restore -p 5433 -C -Fd -j 4 -d postgres /home/postgres/dumpdir/exemdb_01_dir
## [postgres] exemdb_02 Database Restore
/usr/pgsql-12/bin/pg_restore -p 5433 -C -Fd -j 4 -d postgres /home/postgres/dumpdir/exemdb_02_dir더보기
-bash-4.2$ /usr/pgsql-12/bin/pg_restore -v -p 5433 -C -Fd -j 4 -d postgres /home/postgres/dumpdir/exemdb_01_dir
pg_restore: connecting to database for restore
pg_restore: processing item 3051 ENCODING ENCODING
pg_restore: processing item 3052 STDSTRINGS STDSTRINGS
pg_restore: processing item 3053 SEARCHPATH SEARCHPATH
pg_restore: processing item 3054 DATABASE exemdb_01
pg_restore: creating DATABASE "exemdb_01"
pg_restore: connecting to new database "exemdb_01"
pg_restore: processing item 2 EXTENSION pg_stat_statements
pg_restore: creating EXTENSION "pg_stat_statements"
pg_restore: processing item 3055 COMMENT EXTENSION pg_stat_statements
pg_restore: creating COMMENT "EXTENSION pg_stat_statements"
pg_restore: processing item 186 TABLE exemtab_01
pg_restore: creating TABLE "public.exemtab_01"
pg_restore: entering main parallel loop
pg_restore: launching item 3048 TABLE DATA exemtab_01
pg_restore: processing data for table "public.exemtab_01"
pg_restore: finished item 3048 TABLE DATA exemtab_01
pg_restore: launching item 2929 INDEX exemtab_01_ix01
pg_restore: creating INDEX "public.exemtab_01_ix01"
pg_restore: finished item 2929 INDEX exemtab_01_ix01
pg_restore: finished main parallel loop
-bash-4.2$ /usr/pgsql-12/bin/pg_restore -v -p 5433 -C -Fd -j 4 -d postgres /home/postgres/dumpdir/exemdb_02_dir
pg_restore: connecting to database for restore
pg_restore: processing item 3052 ENCODING ENCODING
pg_restore: processing item 3053 STDSTRINGS STDSTRINGS
pg_restore: processing item 3054 SEARCHPATH SEARCHPATH
pg_restore: processing item 3055 DATABASE exemdb_02
pg_restore: creating DATABASE "exemdb_02"
pg_restore: connecting to new database "exemdb_02"
pg_restore: processing item 2 EXTENSION pg_visibility
pg_restore: creating EXTENSION "pg_visibility"
pg_restore: processing item 3056 COMMENT EXTENSION pg_visibility
pg_restore: creating COMMENT "EXTENSION pg_visibility"
pg_restore: processing item 186 TABLE exemtab_02
pg_restore: creating TABLE "public.exemtab_02"
pg_restore: entering main parallel loop
pg_restore: launching item 3049 TABLE DATA exemtab_02
pg_restore: processing data for table "public.exemtab_02"
pg_restore: finished item 3049 TABLE DATA exemtab_02
pg_restore: launching item 2931 INDEX exemtab_02_ix01
pg_restore: creating INDEX "public.exemtab_02_ix01"
pg_restore: finished item 2931 INDEX exemtab_02_ix01
pg_restore: finished main parallel loop
4. PostgreSQL 12 ANALYZE 실행
모든 데이터베이스 통계가 업데이트되도록 전체 Cluster 단위 ANALYZE를 실행합니다.
/usr/pgsql-12/bin/vacuumdb --port=5433 --all --analyze --jobs=4더보기
-bash-4.2$ /usr/pgsql-12/bin/vacuumdb --port=5433 --all --analyze --jobs=4
vacuumdb: vacuuming database "exemdb_01"
vacuumdb: vacuuming database "exemdb_02"
vacuumdb: vacuuming database "postgres"
vacuumdb: vacuuming database "template1"
5. PostgreSQL 12 Upgrade 확인
PostgreSQL Major Upgrade 확인
## [postgres] PostgreSQL 버전과 데이터베이스 리스트 확인
/usr/pgsql-12/bin/psql -p 5433
SELECT VERSION() ;
\l+더보기
-bash-4.2$ /usr/pgsql-12/bin/psql -p 5433
psql (12.12)
Type "help" for help.
postgres=# SELECT VERSION() ;
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 12.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
exemdb_01 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 5903 MB | pg_default |
exemdb_02 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 126 MB | pg_default |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8393 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8049 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8409 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(5 rows)
exemdb_01 데이터베이스 데이터 확인
-- exemdb_01 테이블 Row : 5,000만건 / PG_STAT_STATEMENTS EXTENSION 확인
\c exemdb_01
SELECT COUNT(*) FROM exemtab_01 ;
\dx더보기
postgres=# \c exemdb_01
You are now connected to database "exemdb_01" as user "postgres".
exemdb_01=# SELECT COUNT(*) FROM exemtab_01 ;
count
----------
50000000
(1 row)
exemdb_01=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+-----------------------------------------------------------
pg_stat_statements | 1.7 | public | track execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
exemdb_02 데이터베이스 데이터 확인
-- exemdb_02 테이블 Row : 100만건 / PG_VISIBILITY EXTENSION 확인
\c exemdb_02
SELECT COUNT(*) FROM exemtab_02 ;
\dx더보기
exemdb_01=# \c exemdb_02
You are now connected to database "exemdb_02" as user "postgres".
exemdb_02=# SELECT COUNT(*) FROM exemtab_02 ;
count
---------
1000000
(1 row)
exemdb_02=# \dx
List of installed extensions
Name | Version | Schema | Description
---------------+---------+------------+----------------------------------------------------------------
pg_visibility | 1.2 | public | examine the visibility map (VM) and page-level visibility info
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
| Major Upgrade Using pg_upgrade | 목차 |
이번 단락에서는 pg_upgrade 응용프로그램을 통한 PostgreSQL 9.6에서 PostgreSQL 12로 업그레이드에 대한 내용을 기술합니다.
pg_upgrade를 사용하여, Global Object Dump 및 데이터베이스 Dump를 수행하지 않고 업그레이드를 할 수 있습니다. pg_upgrade는 PostgreSQL 9.6 Data Directory에서 PostgreSQL 12 Data Directory로 복사하여 업그레이드를 수행합니다. 사용자가 pg_dump를 수행하지 않을 뿐이며, pg_upgrade 내부적으로 pg_dump와 pg_restore가 수행됩니다. 따라서 데이터베이스 사이즈가 크다면, 오랜 시간이 걸릴 수 있습니다.
pg_dumpall을 통한 업그레이드와 마찬가지로 Database Cluster의 Backup을 수행하려면 서버에 충분한 디스크 공간이 필요합니다. 업그레이드가 동일한 서버 내에서 수행되는 경우 기존 Database Cluster 사이즈보다 큰 디스크 공간이 있는지 먼저 확인해야 합니다. 새로운 PostgreSQL Database Cluster 전용 공간 등이 필요합니다.
1. PostgreSQL 12 설치 및 설정
PostgreSQL 12 설치
📢 PostgreSQL Install 내용은 [PostgreSQL Setup - Installation] 확인.
PostgreSQL 12 - postgresql.conf 설정
Configuration은 버전별로 차이가 있기 때문에 완벽하게 호환되지 않을 수 있으며, PostgreSQL 12 설치 시 postgresql.conf 내용은 기본값으로 설정되어 있기 때문에 PostgreSQL 9.6과 비교하여 PostgreSQL 12에 필요한 postgresql.conf 내용을 변경해야 합니다. 기존에 설치되어 있는 PostgreSQL 9.6과 Port 충돌을 피하기 위해 PostgreSQL 12에서 사용하는 Port도 변경합니다.
## [postgres] PostgreSQL 12 Configuration 변경
echo "port = 5433" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "listen_addresses = '*'" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "max_connections = 300" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "shared_buffers = 4GB" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "maintenance_work_mem = 64MB" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "autovacuum = off" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "track_counts = off" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "work_mem = 64MB" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "effective_cache_size = 1GB" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "wal_buffers = -1" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "shared_preload_libraries = 'pg_stat_statements'" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "host all all 0.0.0.0/0 trust" >> /home/postgres/PostgreSQL12_Data/pg_hba.conf
## [postgres] PostgreSQL 12 기동
/usr/pgsql-12/bin/pg_ctl -D /home/postgres/PostgreSQL12_Data start
2. Cluster Consistency Check
PostgreSQL 9.6 Cluster와 PostgreSQL 12 Cluster 간에 일관성(Consistency) 검사를 수행합니다. pg_upgrade -c 옵션을 사용하며, 일관성 검사를 위해서는 PostgreSQL이 종료되어 있어야 합니다. 일관성 검사 중 오류가 발생하면, 오류에 대한 내용이 리포팅됩니다. 리포팅된 내용을 참고하여 오류를 해결한 후 다시 일관성 검사를 진행합니다.
📢 pg_upgrade의 사용법 및 설명서는 [PostgreSQL Setup - Version & Utility] 확인.
## [postgres] PostgreSQL 9.6 중지
/usr/pgsql-9.6/bin/pg_ctl -D /home/postgres/PostgreSQL_Data stop## [postgres] PostgreSQL 12 중지
/usr/pgsql-12/bin/pg_ctl -D /home/postgres/PostgreSQL12_Data stop## [postgres] Cluster Consistency Check
## -c 옵션 : 일관성(Consistency) 검사
## -b 옵션 : (구) PostgreSQL bin Directory (PostgreSQL 9.6 bin Directory)
## -B 옵션 : (신) PostgreSQL bin Directory (PostgreSQL 12 bin Directory)
## -d 옵션 : (구) PostgreSQL Data Directory (PostgreSQL 9.6 Data Directory)
## -D 옵션 : (신) PostgreSQL Data Directory (PostgreSQL 12 Data Directory)
/usr/pgsql-12/bin/pg_upgrade -c \
-b /usr/pgsql-9.6/bin \
-B /usr/pgsql-12/bin \
-d /home/postgres/PostgreSQL_Data \
-D /home/postgres/PostgreSQL12_Data더보기
-bash-4.2$ /usr/pgsql-12/bin/pg_upgrade -c \
> -b /usr/pgsql-9.6/bin \
> -B /usr/pgsql-12/bin \
> -d /home/postgres/PostgreSQL_Data \
> -D /home/postgres/PostgreSQL12_Data
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for tables WITH OIDS ok
Checking for invalid "sql_identifier" user columns ok
Checking for invalid "unknown" user columns ok
Checking for hash indexes ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok
*Clusters are compatible*
3. Cluster Upgrade
3. Cluster Consistency Check가 통과되면, Cluster Upgrade를 수행합니다. Upgrade를 위해서는 PostgreSQL이 종료되어 있어야 합니다. pg_upgrade는 PostgreSQL 9.6 Data Directory에서 PostgreSQL 12 Data Directory로 복사하여 업그레이드를 수행합니다. 데이터베이스 사이즈가 크다면, 오랜 시간이 걸릴 수 있습니다.
## [postgres] CLuster Upgrade
/usr/pgsql-12/bin/pg_upgrade \
-b /usr/pgsql-9.6/bin \
-B /usr/pgsql-12/bin \
-d /home/postgres/PostgreSQL_Data \
-D /home/postgres/PostgreSQL12_Data더보기
-bash-4.2$ /usr/pgsql-12/bin/pg_upgrade \
> -b /usr/pgsql-9.6/bin \
> -B /usr/pgsql-12/bin \
> -d /home/postgres/PostgreSQL_Data \
> -D /home/postgres/PostgreSQL12_Data
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for tables WITH OIDS ok
Checking for invalid "sql_identifier" user columns ok
Checking for invalid "unknown" user columns ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.
Performing Upgrade
------------------
Analyzing all rows in the new cluster ok
Freezing all rows in the new cluster ok
Deleting files from new pg_xact ok
Copying old pg_clog to new server ok
Setting oldest XID for new cluster ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsets ok
Copying old pg_multixact/offsets to new server ok
Deleting files from new pg_multixact/members ok
Copying old pg_multixact/members to new server ok
Setting next multixact ID and offset for new cluster ok
Resetting WAL archives ok
Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
ok
Copying user relation files
ok
Setting next OID for new cluster ok
Sync data directory to disk ok
Creating script to analyze new cluster ok
Creating script to delete old cluster ok
Checking for hash indexes ok
Checking for extension updates notice
Your installation contains extensions that should be updated
with the ALTER EXTENSION command. The file
update_extensions.sql
when executed by psql by the database superuser will update
these extensions.
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
./analyze_new_cluster.sh
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh📢 본 테스트에서는 extension updates의 notice 발생.
pg_upgrade 중 발생한 문제에 대해서는 스크립트 파일로 해결방법을 제공합니다. 제공된 스크립트 파일을 수행하여 문제를 해결할 수 있습니다.
## [postgres] PostgreSQL 12 기동하여 pg_upgrade 중 발생한 문제 해결
psql -p 5433 -f update_extensions.sql
4. PostgreSQL 12 기동 및 ANALYZE 실행
모든 데이터베이스 통계가 업데이트되도록 전체 Cluster 단위 ANALYZE를 실행합니다.
## [postgres] PostgreSQL 12 기동
/usr/pgsql-12/bin/pg_ctl -D /home/postgres/PostgreSQL12_Data start/usr/pgsql-12/bin/vacuumdb --port=5433 --all --analyze --jobs=4더보기
-bash-4.2$ /usr/pgsql-12/bin/vacuumdb --port=5433 --all --analyze --jobs=4
vacuumdb: vacuuming database "exemdb_01"
vacuumdb: vacuuming database "exemdb_02"
vacuumdb: vacuuming database "postgres"
vacuumdb: vacuuming database "template1"
5. PostgreSQL 12 Upgrade 확인
PostgreSQL Major Upgrade 확인
## [postgres] PostgreSQL 버전과 데이터베이스 리스트 확인
/usr/pgsql-12/bin/psql -p 5433
SELECT VERSION() ;
\l+더보기
-bash-4.2$ /usr/pgsql-12/bin/psql -p 5433
psql (12.12)
Type "help" for help.
postgres=# SELECT VERSION() ;
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 12.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
exemdb_01 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 5903 MB | pg_default |
exemdb_02 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 126 MB | pg_default |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8337 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8065 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres+| 8273 kB | pg_default | default template for new databases
| | | | | =c/postgres | | |
(5 rows)
exemdb_01 데이터베이스 데이터 확인
-- exemdb_01 테이블 Row : 5,000만건 / PG_STAT_STATEMENTS EXTENSION 확인
\c exemdb_01
SELECT COUNT(*) FROM exemtab_01 ;
\dx더보기
postgres=# \c exemdb_01
You are now connected to database "exemdb_01" as user "postgres".
exemdb_01=# SELECT COUNT(*) FROM exemtab_01 ;
count
----------
50000000
(1 row)
exemdb_01=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+-----------------------------------------------------------
pg_stat_statements | 1.7 | public | track execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
exemdb_02 데이터베이스 데이터 확인
-- exemdb_02 테이블 Row : 100만건 / PG_VISIBILITY EXTENSION 확인
\c exemdb_02
SELECT COUNT(*) FROM exemtab_02 ;
\dx더보기
exemdb_01=# \c exemdb_02
You are now connected to database "exemdb_02" as user "postgres".
exemdb_02=# SELECT COUNT(*) FROM exemtab_02 ;
count
---------
1000000
(1 row)
exemdb_02=# \dx
List of installed extensions
Name | Version | Schema | Description
---------------+---------+------------+----------------------------------------------------------------
pg_visibility | 1.2 | public | examine the visibility map (VM) and page-level visibility info
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
| Major Upgrade Using pg_upgrade Link Option | 목차 |
이번 단락에서는 pg_upgrade 응용프로그램의 Link 옵션을 통한 PostgreSQL 9.6에서 PostgreSQL 12로 업그레이드에 대한 내용을 기술합니다.
앞서 설명한 pg_upgrade를 통한 업그레이드는 데이터베이스 클러스터를 복사하여 업그레이드하는 방식으로 데이터베이스 크기가 클 경우 매우 오랜 시간이 걸릴 수 있습니다. 이러한 이유로 pg_upgrade의 -k 옵션을 이용하여 데이터베이스 클러스터를 복사하지 않고 Hard Link를 이용하여 업그레이드를 할 수 있습니다. Link를 사용하기 때문에, 동일 서버(파일시스템) 내에서만 사용이 가능하며 몇 초안으로 업그레이드를 완료할 수 있습니다.
1. PostgreSQL 12 설치 및 설정
PostgreSQL 12 설치
📢 PostgreSQL Install 내용은 [PostgreSQL Setup - Installation] 확인.
PostgreSQL 12 - postgresql.conf 설정
Configuration은 버전별로 차이가 있기 때문에 완벽하게 호환되지 않을 수 있으며, PostgreSQL 12 설치 시 postgresql.conf 내용은 기본값으로 설정되어 있기 때문에 PostgreSQL 9.6과 비교하여 PostgreSQL 12에 필요한 postgresql.conf 내용을 변경해야 합니다. 기존에 설치되어 있는 PostgreSQL 9.6과 Port 충돌을 피하기 위해 PostgreSQL 12에서 사용하는 Port도 변경합니다.
## [postgres] PostgreSQL 12 Configuration 변경
echo "port = 5433" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "listen_addresses = '*'" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "max_connections = 300" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "shared_buffers = 4GB" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "maintenance_work_mem = 64MB" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "autovacuum = off" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "track_counts = off" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "work_mem = 64MB" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "effective_cache_size = 1GB" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "wal_buffers = -1" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "shared_preload_libraries = 'pg_stat_statements'" >> /home/postgres/PostgreSQL12_Data/postgresql.auto.conf
echo "host all all 0.0.0.0/0 trust" >> /home/postgres/PostgreSQL12_Data/pg_hba.conf
## [postgres] PostgreSQL 12 기동
/usr/pgsql-12/bin/pg_ctl -D /home/postgres/PostgreSQL12_Data start
2. Cluster Consistency Check
PostgreSQL 9.6 Cluster와 PostgreSQL 12 Cluster 간에 일관성(Consistency) 검사를 수행합니다. pg_upgrade -c 옵션을 사용하며, 일관성 검사를 위해서는 PostgreSQL이 종료되어 있어야 합니다. 일관성 검사 중 오류가 발생하면, 오류에 대한 내용이 리포팅됩니다. 리포팅된 내용을 참고하여 오류를 해결한 후 다시 일관성 검사를 진행합니다.
📢 pg_upgrade의 사용법 및 설명서는 [PostgreSQL Setup - Version & Utility] 확인.
## [postgres] PostgreSQL 9.6 중지
/usr/pgsql-9.6/bin/pg_ctl -D /home/postgres/PostgreSQL_Data stop## [postgres] PostgreSQL 12 중지
/usr/pgsql-12/bin/pg_ctl -D /home/postgres/PostgreSQL12_Data stop## [postgres] Cluster Consistency Check
## -c 옵션 : 일관성(Consistency) 검사
## -b 옵션 : (구) PostgreSQL bin Directory (PostgreSQL 9.6 bin Directory)
## -B 옵션 : (신) PostgreSQL bin Directory (PostgreSQL 12 bin Directory)
## -d 옵션 : (구) PostgreSQL Data Directory (PostgreSQL 9.6 Data Directory)
## -D 옵션 : (신) PostgreSQL Data Directory (PostgreSQL 12 Data Directory)
/usr/pgsql-12/bin/pg_upgrade -c \
-b /usr/pgsql-9.6/bin \
-B /usr/pgsql-12/bin \
-d /home/postgres/PostgreSQL_Data \
-D /home/postgres/PostgreSQL12_Data더보기
-bash-4.2$ /usr/pgsql-12/bin/pg_upgrade -c \
> -b /usr/pgsql-9.6/bin \
> -B /usr/pgsql-12/bin \
> -d /home/postgres/PostgreSQL_Data \
> -D /home/postgres/PostgreSQL12_Data
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for tables WITH OIDS ok
Checking for invalid "sql_identifier" user columns ok
Checking for invalid "unknown" user columns ok
Checking for hash indexes ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok
*Clusters are compatible*
3. Cluster Upgrade
3. Cluster Consistency Check가 통과되면, Cluster Upgrade를 수행합니다. Upgrade를 위해서는 PostgreSQL이 종료되어 있어야 합니다. pg_upgrade는 PostgreSQL 9.6 Data Directory에서 PostgreSQL 12 Data Directory로 복사하여 업그레이드를 수행합니다.
## [postgres] CLuster Upgrade
/usr/pgsql-12/bin/pg_upgrade -k \
-b /usr/pgsql-9.6/bin \
-B /usr/pgsql-12/bin \
-d /home/postgres/PostgreSQL_Data \
-D /home/postgres/PostgreSQL12_Data더보기
-bash-4.2$ /usr/pgsql-12/bin/pg_upgrade -k \
> -b /usr/pgsql-9.6/bin \
> -B /usr/pgsql-12/bin \
> -d /home/postgres/PostgreSQL_Data \
> -D /home/postgres/PostgreSQL12_Data
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for tables WITH OIDS ok
Checking for invalid "sql_identifier" user columns ok
Checking for invalid "unknown" user columns ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.
Performing Upgrade
------------------
Analyzing all rows in the new cluster ok
Freezing all rows in the new cluster ok
Deleting files from new pg_xact ok
Copying old pg_clog to new server ok
Setting oldest XID for new cluster ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsets ok
Copying old pg_multixact/offsets to new server ok
Deleting files from new pg_multixact/members ok
Copying old pg_multixact/members to new server ok
Setting next multixact ID and offset for new cluster ok
Resetting WAL archives ok
Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
ok
Adding ".old" suffix to old global/pg_control ok
If you want to start the old cluster, you will need to remove
the ".old" suffix from /home/postgres/PostgreSQL_Data/global/pg_control.old.
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.
Linking user relation files
ok
Setting next OID for new cluster ok
Sync data directory to disk ok
Creating script to analyze new cluster ok
Creating script to delete old cluster ok
Checking for hash indexes ok
Checking for extension updates notice
Your installation contains extensions that should be updated
with the ALTER EXTENSION command. The file
update_extensions.sql
when executed by psql by the database superuser will update
these extensions.
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
./analyze_new_cluster.sh
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh📢 본 테스트에서는 extension updates의 notice 발생.
pg_upgrade 중 발생한 문제에 대해서는 스크립트 파일로 해결방법을 제공합니다. 제공된 스크립트 파일을 수행하여 문제를 해결할 수 있습니다.
## [postgres] PostgreSQL 12 기동하여 pg_upgrade 중 발생한 문제 해결
psql -p 5433 -f update_extensions.sql
4. PostgreSQL 12 기동 및 ANALYZE 실행
모든 데이터베이스 통계가 업데이트되도록 전체 Cluster 단위 ANALYZE를 실행합니다.
## [postgres] PostgreSQL 12 기동
/usr/pgsql-12/bin/pg_ctl -D /home/postgres/PostgreSQL12_Data start/usr/pgsql-12/bin/vacuumdb --port=5433 --all --analyze --jobs=4더보기
-bash-4.2$ /usr/pgsql-12/bin/vacuumdb --port=5433 --all --analyze --jobs=4
vacuumdb: vacuuming database "exemdb_01"
vacuumdb: vacuuming database "exemdb_02"
vacuumdb: vacuuming database "postgres"
vacuumdb: vacuuming database "template1"
5. PostgreSQL 12 Upgrade 확인
PostgreSQL Major Upgrade 확인
## [postgres] PostgreSQL 버전과 데이터베이스 리스트 확인
/usr/pgsql-12/bin/psql -p 5433
SELECT VERSION() ;
\l+더보기
-bash-4.2$ /usr/pgsql-12/bin/psql -p 5433
psql (12.12)
Type "help" for help.
postgres=# SELECT VERSION() ;
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 12.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
exemdb_01 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 5903 MB | pg_default |
exemdb_02 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 126 MB | pg_default |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8473 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8065 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres+| 8217 kB | pg_default | default template for new databases
| | | | | =c/postgres | | |
(5 rows)
exemdb_01 데이터베이스 데이터 확인
-- exemdb_01 테이블 Row : 5,000만건 / PG_STAT_STATEMENTS EXTENSION 확인
\c exemdb_01
SELECT COUNT(*) FROM exemtab_01 ;
\dx더보기
postgres=# \c exemdb_01
You are now connected to database "exemdb_01" as user "postgres".
exemdb_01=# SELECT COUNT(*) FROM exemtab_01 ;
count
----------
50000000
(1 row)
exemdb_01=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+-----------------------------------------------------------
pg_stat_statements | 1.7 | public | track execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
exemdb_02 데이터베이스 데이터 확인
-- exemdb_02 테이블 Row : 100만건 / PG_VISIBILITY EXTENSION 확인
\c exemdb_02
SELECT COUNT(*) FROM exemtab_02 ;
\dx더보기
exemdb_01=# \c exemdb_02
You are now connected to database "exemdb_02" as user "postgres".
exemdb_02=# SELECT COUNT(*) FROM exemtab_02 ;
count
---------
1000000
(1 row)
exemdb_02=# \dx
List of installed extensions
Name | Version | Schema | Description
---------------+---------+------------+----------------------------------------------------------------
pg_visibility | 1.2 | public | examine the visibility map (VM) and page-level visibility info
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
| Major Upgrade Using pg_upgradecluster(Only Debian계열) | 목차 |
이번 단락에서는 pg_upgradecluster 응용프로그램을 통한 PostgreSQL 9.6에서 PostgreSQL 12로 업그레이드에 대한 내용을 기술합니다.
pg_upgradecluster는 기존 PostgreSQL Cluster를 새로운 Major버전으로 업그레이드합니다. Debian계열(Ubuntu 등)에서만 제공되는 방법으로, pg_dumpall, pg_upgrade 보다 쉽게 업그레이드가 가능합니다.
📢 pg_upgradecluster, pg_lsclusters, pg_dropcluster의 사용법 및 설명서는 [PostgreSQL Setup - Version & Utility] 확인.
1. PostgreSQL 12 설치 및 설정
PostgreSQL 12 설치
📢 PostgreSQL Install 내용은 [PostgreSQL Setup - Installation] 확인.
pg_upgradecluster를 사용한 Upgrade는 Cluster가 구성되어 있지 않아도 됩니다. Ubuntu에서는 PostgreSQL 설치 시 Cluster를 자동으로 구성하기 때문에 신규 버전의 Cluster를 삭제합니다.
PostgreSQL Cluster 확인
root@ubuntu:~# pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
9.6 main 5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
12 main 5433 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
PostgreSQL 12 Cluster 삭제
root@ubuntu:~# pg_dropcluster --stop 12 main
2. PostgreSQL Cluster 업그레이드
ppg_upgradecluster를 수행하면, Configuration File도 자동으로 업그레이드됩니다.
- pg_hba.conf : PostgreSQL 9.6에서 사용하던 파일 Copy&Paste
- postgresql.conf : PostgreSQL 9.6에서 사용하던 내용을 PostgreSQL 12 버전에 맞게 변경합니다. 업그레이드 시 PostgreSQL 9.6에서 중이던 파라미터는 옮겨지지만, PostgreSQL 12 버전에 추가된 파라미터정보는 포함되어 있지 않습니다. 따라서, PostgreSQL 12에 추가된 파라미터를 사용하기 위해서는 별도의 수정이 필요합니다.
pg_upgradecluster -v 12 9.6 main더보기
root@ubuntu:~# pg_upgradecluster -v 12 9.6 main
Stopping old cluster...
Restarting old cluster with restricted connections...
Notice: extra pg_ctl/postgres options given, bypassing systemctl for start operation
Creating new PostgreSQL cluster 12/main ...
/usr/lib/postgresql/12/bin/initdb -D /var/lib/postgresql/12/main --auth-local peer --auth-host md5 --encoding UTF8 --lc-collate en_US.UTF-8 --lc-ctype en_US.UTF-8
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/postgresql/12/main ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
Success. You can now start the database server using:
pg_ctlcluster 12 main start
Copying old configuration files...
Copying old start.conf...
Copying old pg_ctl.conf...
Starting new cluster...
Notice: extra pg_ctl/postgres options given, bypassing systemctl for start operation
Roles, databases, schemas, ACLs...
set_config
------------
(1 row)
set_config
------------
(1 row)
set_config
------------
(1 row)
set_config
------------
(1 row)
set_config
------------
(1 row)
set_config
------------
(1 row)
Fixing hardcoded library paths for stored procedures...
Upgrading database exemdb_01...
Analyzing database exemdb_01...
Fixing hardcoded library paths for stored procedures...
Upgrading database postgres...
Analyzing database postgres...
Fixing hardcoded library paths for stored procedures...
Upgrading database template1...
Analyzing database template1...
Fixing hardcoded library paths for stored procedures...
Upgrading database exemdb_02...
Analyzing database exemdb_02...
Stopping target cluster...
Stopping old cluster...
Disabling automatic startup of old cluster...
Starting upgraded cluster on port 5432...
Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with
pg_dropcluster 9.6 main
Ver Cluster Port Status Owner Data directory Log file
9.6 main 5433 down postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
Ver Cluster Port Status Owner Data directory Log file
12 main 5432 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
3. PostgreSQL 12 Upgrade 확인
PostgreSQL Major Upgrade 확인
## [postgres] PostgreSQL 버전과 데이터베이스 리스트 확인
/usr/lib/postgresql/12/bin/psql -p 5432
SELECT VERSION() ;
\l+더보기
postgres@ubuntu:~$ /usr/lib/postgresql/12/bin/psql -p 5432
psql (12.12 (Ubuntu 12.12-1.pgdg22.04+1))
Type "help" for help.
postgres=# SELECT VERSION() ;
version
--------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 12.12 (Ubuntu 12.12-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.2.0-19ubuntu1) 11.2.0, 64-bit
(1 row)
postgres@ubuntu:~$ \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
exemdb_01 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 597 MB | pg_default |
exemdb_02 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 126 MB | pg_default |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8073 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7825 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8073 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(5 rows)
exemdb_01 데이터베이스 데이터 확인
-- exemdb_01 테이블 Row : 5,000만건 / PG_STAT_STATEMENTS EXTENSION 확인
\c exemdb_01
SELECT COUNT(*) FROM exemtab_01 ;
\dx더보기
postgres=# \c exemdb_01
You are now connected to database "exemdb_01" as user "postgres".
exemdb_01=# SELECT COUNT(*) FROM exemtab_01 ;
count
---------
5000000
(1 row)
exemdb_01=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+-----------------------------------------------------------
pg_stat_statements | 1.7 | public | track execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
exemdb_02 데이터베이스 데이터 확인
-- exemdb_02 테이블 Row : 100만건 / PG_VISIBILITY EXTENSION 확인
\c exemdb_02
SELECT COUNT(*) FROM exemtab_02 ;
\dx더보기
exemdb_01=# \c exemdb_02
You are now connected to database "exemdb_02" as user "postgres".
exemdb_02=# SELECT COUNT(*) FROM exemtab_02 ;
count
---------
1000000
(1 row)
exemdb_02=# \dx
List of installed extensions
Name | Version | Schema | Description
---------------+---------+------------+----------------------------------------------------------------
pg_visibility | 1.2 | public | examine the visibility map (VM) and page-level visibility info
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
기획 및 글 | 기술기획팀
'엑셈 경쟁력 > DB 인사이드' 카테고리의 다른 글
| DB 인사이드 | PostgreSQL Vacuum - Monitoring : XMIN’s Horizon (2) | 2023.01.19 |
|---|---|
| DB 인사이드 | PostgreSQL Setup - Migration & Upgrade 성능 및 주의사항 (0) | 2022.11.23 |
| DB 인사이드 | PostgreSQL Setup - Minor Upgrade (0) | 2022.09.28 |
| DB 인사이드 | PostgreSQL Setup - Installation (0) | 2022.08.25 |
| DB 인사이드 | PostgreSQL Setup - Version & Utility (0) | 2022.08.25 |
댓글