pg_dump is used for single or specified database backups, so if you need whole cluster upgrade you need to run more than one pg_dump commands. pg_dump do not dump global objects (roles, tablespaces, privileges etc.). for global objects migration yo need a pg_dumpall script.
you can take backup dump in a few formats with pg_dump. If you take a custom format then you must use pg_restore to restore the dump file. if you use plain text, pg_dump generates a script file with sql commands so you can restore dump file just with psql.
!!!The most important point to remember is that both dump and restore should be performed using the latest binaries. For example, if we need to migrate from version 9.3 to version 11, we should be using the pg_dump binary of PostgreSQL 11 to connect to 9.3 .
ref : https://www.percona.com/blog/2019/03/27/postgresql-upgrade-using-pg_dump-pg_restore/
pg_dump is suitable for specified database upgrades, not whole cluster upgrade, as I mentioned above. So I will upgrade two databases and tray to migrate global objects.
step : install postgresql 13 on destination database.
yum install postgresql13 postgresql13-server.x86_64
extra step: I will create some tablespaces, then creates tables in new tablespaces. then grant some privileges for tables. my purpose is check the results on destination size.
im my case I will not create new tablespaces path on destination side (13). after all, I will check tablespaces, privileges, roles on destination side.
!!!create new users/roles
postgres=# create role deneme with login;
CREATE ROLE
postgres=# create role deneme2 with superuser;
CREATE ROLE
postgres=# create role deneme3 with createdb;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
deneme | | {}
deneme2 | Superuser, Cannot login | {}
deneme3 | Create DB, Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
!!!create new tablespaces
test=# \d pg_tablespace;
Table "pg_catalog.pg_tablespace"
Column | Type | Collation | Nullable | Default
------------+-----------+-----------+----------+---------
spcname | name | | not null |
spcowner | oid | | not null |
spcacl | aclitem[] | | |
spcoptions | text[] | | |
Indexes:
"pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
"pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"
test=# select spcname, spcowner, spcacl, spcoptions from pg_catalog.pg_tablespace;
spcname | spcowner | spcacl | spcoptions
------------+----------+--------+------------
pg_default | 10 | |
pg_global | 10 | |
(2 rows)
test=# create tablespace tbs_index owner deneme location '/var/lib/pgsql/9.6/data/tbs_indexes';
WARNING: tablespace location should not be inside the data directory
CREATE TABLESPACE
test=# create tablespace tbs_test owner deneme3 location '/var/lib/pgsql/9.6/data/tbs_test';
WARNING: tablespace location should not be inside the data directory
CREATE TABLESPACE
test=# create tablespace tbs_space location '/var/lib/pgsql/9.6/data';
WARNING: tablespace location should not be inside the data directory
CREATE TABLESPACE
test=# select spcname, spcowner, spcacl, spcoptions from pg_catalog.pg_tablespace;
spcname | spcowner | spcacl | spcoptions
------------+----------+--------+------------
pg_default | 10 | |
pg_global | 10 | |
tbs_index | 16789 | |
tbs_test | 16791 | |
tbs_space | 10 | |
(5 rows)
test=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options
| Size | Description
------------+----------+-------------------------------------+-------------------+---------
+---------+-------------
pg_default | postgres | | |
| 16 GB |
pg_global | postgres | | |
| 497 kB |
tbs_index | deneme | /var/lib/pgsql/9.6/data/tbs_indexes | |
| 0 bytes |
tbs_space | postgres | /var/lib/pgsql/9.6/data | |
| 0 bytes |
tbs_test | deneme3 | /var/lib/pgsql/9.6/data/tbs_test | |
| 0 bytes |
(5 rows)
!!!create new tables in newly created tablespaces
test=# create table foot1 (a int) tablespace tbs_index;
CREATE TABLE
test=# create table foot2 (a int) tablespace tbs_test;
CREATE TABLE
test=# create table foot2 (a int) tablespace tbs_space;
ERROR: relation "foot2" already exists
test=# create table foot3 (a int) tablespace tbs_space;
CREATE TABLE
test=# set role deneme3;
SET
test=> SELECT SESSION_USER, CURRENT_USER;
session_user | current_user
--------------+--------------
postgres | deneme3
(1 row)
test=> create table foot4 (a int) tablespace tbs_space;
ERROR: permission denied for tablespace tbs_space
test=> create table foot4 (a int) tablespace tbs_test;
CREATE TABLE
test=> create table foot4 (a int) tablespace tbs_index;
ERROR: permission denied for tablespace tbs_index
test=>
test=>
test=> set role deneme2;
SET
test=# create table foot5 (a int) tablespace tbs_index;
CREATE TABLE
test=# create table foot6 (a int) tablespace tbs_test;
CREATE TABLE
test=# create table foot7 (a int) tablespace tbs_space;
CREATE TABLE
template1=# \c test
psql (13.2, server 9.6.21)
You are now connected to database "test" as user "postgres".
test=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------+-------+----------
public | foot1 | table | postgres
public | foot2 | table | postgres
public | foot3 | table | postgres
public | foot4 | table | deneme3
public | foot5 | table | deneme2
public | foot6 | table | deneme2
public | foot7 | table | deneme2
public | t50 | table | postgres
public | t51 | table | postgres
public | t52 | table | postgres
public | t53 | table | postgres
public | t54 | table | postgres
public | t55 | table | postgres
public | t56 | table | postgres
public | t57 | table | postgres
public | t58 | table | postgres
public | t59 | table | postgres
public | t60 | table | postgres
public | t61 | table | postgres
public | t62 | table | postgres
public | t63 | table | postgres
public | t64 | table | postgres
(22 rows)
!!! grant some privileges
test=# grant select on t64 to deneme3;
GRANT
test=# grant select on public.t64 to deneme3;
GRANT
test=# grant select on public.t62 to deneme3;
GRANT
step : take a dump file for global objects
/usr/lib/postgresql/13/bin/pg_dumpall -g -p 5432 -h 192.168.56.129 > /tmp/globals_only.sql
step: take a dump file for postgresql and test databases. because we need to migrate this 2 databases, as advantage of pg_dump we can migrate databases that just we need.
-bash-4.2$ /usr/pgsql-9.6/bin/pg_dump postgres > /tmp/postgres96_dump.sql
-bash-4.2$ scp /tmp/postgres96_dump.sql root@192.168.56.130:/tmp
-bash-4.2$ /usr/pgsql-9.6/bin/pg_dump test > /tmp/test96_dump.sql
-bash-4.2$ scp /tmp/test96_dump.sql root@192.168.56.130:/tmp
step : lets import dump file “/tmp/globals_only.sql” on destination (13) server. We will face some errors because I have not created the file path for non default tablespaces. in the destinaton side I do not need non-default tablespaces. I have enough space in default data directory at new side . so I did not create directories for non-default tablespaces. Do not worry about errors, postgresql create table in new side(13) at default data path even if dump launch errors like “ERROR: directory “xxx” does not exist”. As you can see in output of globals.sql below some roles has been created. after import has finished I will check new roles.
-bash-4.2$ /usr/pgsql-13/bin/psql -f /tmp/globals.sql
SET
SET
SET
CREATE ROLE
ALTER ROLE
CREATE ROLE
ALTER ROLE
CREATE ROLE
ALTER ROLE
psql:/tmp/globals.sql:20: ERROR: role "postgres" already exists
ALTER ROLE
psql:/tmp/globals.sql:30: ERROR: directory "/var/lib/pgsql/9.6/data/tbs_indexes" does not exist
psql:/tmp/globals.sql:31: ERROR: directory "/var/lib/pgsql/9.6/data/tbs_test" does not exist
psql:/tmp/globals.sql:32: ERROR: directory "/var/lib/pgsql/9.6/data" does not exist
-bash-4.2$
-bash-4.2$
-bash-4.2$ psql
psql (13.2)
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
deneme | | {}
deneme2 | Superuser, Cannot login | {}
deneme3 | Create DB, Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
step: now we can import dump files creted with pg_dump. First I imported posgresql database dump file.
-bash-4.2$ /usr/pgsql-13/bin/psql -d postgres -f /tmp/postgres96_dump.sql
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
COMMENT
CREATE EXTENSION
COMMENT
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
COPY 5
COPY 1000
COPY 1000
COPY 1000
COPY 1000
COPY 1000
COPY 1000
COPY 1000
COPY 1000
COPY 1000
COPY 5
COPY 100000
COPY 100000
COPY 100000
COPY 100000
COPY 100000
COPY 100000
COPY 100000
COPY 100000
COPY 100000
COPY 100000
COPY 5
COPY 10000000
COPY 10000000
COPY 10000000
COPY 10000000
COPY 10000000
COPY 10000000
COPY 10000000
COPY 10000000
COPY 10000000
COPY 10000000
COPY 5
COPY 100000000
COPY 5
COPY 5
COPY 5
COPY 5
COPY 5
GRANT
step: check tables after import. my individual tables has created, that looks everyting is fine. privileges created on my custom tables. priviles had given to deneme3 on t11 table has created also.
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | postgres
public | t11 | table | postgres
public | t12 | table | postgres
public | t13 | table | postgres
public | t14 | table | postgres
public | t15 | table | postgres
public | t16 | table | postgres
public | t17 | table | postgres
public | t18 | table | postgres
public | t19 | table | postgres
public | t2 | table | postgres
public | t20 | table | postgres
public | t21 | table | postgres
public | t22 | table | postgres
public | t23 | table | postgres
public | t24 | table | postgres
public | t25 | table | postgres
public | t26 | table | postgres
public | t27 | table | postgres
public | t28 | table | postgres
public | t29 | table | postgres
public | t3 | table | postgres
public | t30 | table | postgres
public | t31 | table | postgres
public | t32 | table | postgres
public | t33 | table | postgres
public | t34 | table | postgres
public | t35 | table | postgres
public | t36 | table | postgres
public | t37 | table | postgres
public | t38 | table | postgres
public | t39 | table | postgres
public | t4 | table | postgres
public | t40 | table | postgres
public | t5 | table | postgres
public | t6 | table | postgres
public | t7 | table | postgres
public | t8 | table | postgres
public | t9 | table | postgres
(39 rows)
postgres=# SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name='t11';
grantee | privilege_type
----------+----------------
postgres | INSERT
postgres | SELECT
postgres | UPDATE
postgres | DELETE
postgres | TRUNCATE
postgres | REFERENCES
postgres | TRIGGER
deneme3 | SELECT
(8 rows)
step:now we can import “test” database to dsestination side (13). It is normal that we will face some errors about tablespaces. Because I did not create tablespaces exist on source side (9.6). you can check it on previously steps.
-bash-4.2$ psql --command="create database test"
CREATE DATABASE
-bash-4.2$ /usr/pgsql-13/bin/psql -d test -f /tmp/test96_dump.sql
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
psql:/tmp/test96_dump.sql:33: ERROR: invalid value for parameter "default_tablespace": "tbs_index"
DETAIL: Tablespace "tbs_index" does not exist.
SET
CREATE TABLE
ALTER TABLE
psql:/tmp/test96_dump.sql:48: ERROR: invalid value for parameter "default_tablespace": "tbs_test"
DETAIL: Tablespace "tbs_test" does not exist.
CREATE TABLE
ALTER TABLE
psql:/tmp/test96_dump.sql:61: ERROR: invalid value for parameter "default_tablespace": "tbs_space"
DETAIL: Tablespace "tbs_space" does not exist.
CREATE TABLE
ALTER TABLE
psql:/tmp/test96_dump.sql:74: ERROR: invalid value for parameter "default_tablespace": "tbs_test"
DETAIL: Tablespace "tbs_test" does not exist.
CREATE TABLE
ALTER TABLE
psql:/tmp/test96_dump.sql:87: ERROR: invalid value for parameter "default_tablespace": "tbs_index"
DETAIL: Tablespace "tbs_index" does not exist.
CREATE TABLE
ALTER TABLE
psql:/tmp/test96_dump.sql:100: ERROR: invalid value for parameter "default_tablespace": "tbs_test"
DETAIL: Tablespace "tbs_test" does not exist.
CREATE TABLE
ALTER TABLE
psql:/tmp/test96_dump.sql:113: ERROR: invalid value for parameter "default_tablespace": "tbs_space"
DETAIL: Tablespace "tbs_space" does not exist.
CREATE TABLE
ALTER TABLE
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
COPY 100000
COPY 100000
COPY 100000
COPY 100000
COPY 100000
COPY 100000
COPY 100000
COPY 100000
COPY 100000
COPY 100000
COPY 10000000
COPY 10000000
COPY 10000000
COPY 10000000
COPY 10000000
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
step:lets check object grants on test database. all grants that given in source (9.6) has created. As you remember, I have some tables in non-default tablespaces and that non-default tablespaces has not migrated new side (13). my tables tgat created in non-default tablespaces migrated successfully also, with a small difeerence, they created in default tablespace in new side(13).
test=# SELECT grantee, privilege_type, table_name from information_schema.role_table_grants where table_schema='public' and table_catalog='test';
grantee | privilege_type | table_name
----------+----------------+------------
deneme3 | INSERT | foot4
deneme3 | SELECT | foot4
deneme3 | UPDATE | foot4
deneme3 | DELETE | foot4
deneme3 | TRUNCATE | foot4
deneme3 | REFERENCES | foot4
deneme3 | TRIGGER | foot4
deneme2 | INSERT | foot7
deneme2 | SELECT | foot7
deneme2 | UPDATE | foot7
deneme2 | DELETE | foot7
deneme2 | TRUNCATE | foot7
deneme2 | REFERENCES | foot7
deneme2 | TRIGGER | foot7
deneme3 | SELECT | foot7
postgres | INSERT | t50
postgres | SELECT | t50
postgres | UPDATE | t50
postgres | DELETE | t50
postgres | TRUNCATE | t50
postgres | REFERENCES | t50
postgres | TRIGGER | t50
deneme3 | SELECT | t50
postgres | INSERT | t51
postgres | SELECT | t51
postgres | UPDATE | t51
postgres | DELETE | t51
postgres | TRUNCATE | t51
postgres | REFERENCES | t51
postgres | TRIGGER | t51
deneme3 | SELECT | t51
postgres | INSERT | t52
postgres | SELECT | t52
postgres | UPDATE | t52
postgres | DELETE | t52
postgres | TRUNCATE | t52
postgres | REFERENCES | t52
postgres | TRIGGER | t52
deneme3 | SELECT | t52
postgres | INSERT | t53
postgres | SELECT | t53
postgres | UPDATE | t53
postgres | DELETE | t53
postgres | TRUNCATE | t53
postgres | REFERENCES | t53
postgres | TRIGGER | t53
deneme3 | SELECT | t53
postgres | INSERT | t54
postgres | SELECT | t54
postgres | UPDATE | t54
postgres | DELETE | t54
postgres | TRUNCATE | t54
postgres | REFERENCES | t54
postgres | TRIGGER | t54
deneme3 | SELECT | t54
postgres | INSERT | t55
postgres | SELECT | t55
postgres | UPDATE | t55
postgres | DELETE | t55
postgres | TRUNCATE | t55
postgres | REFERENCES | t55
postgres | TRIGGER | t55
deneme3 | SELECT | t55
postgres | INSERT | t56
postgres | SELECT | t56
postgres | UPDATE | t56
postgres | DELETE | t56
postgres | TRUNCATE | t56
postgres | REFERENCES | t56
postgres | TRIGGER | t56
deneme3 | SELECT | t56
postgres | INSERT | t57
postgres | SELECT | t57
postgres | UPDATE | t57
postgres | DELETE | t57
postgres | TRUNCATE | t57
postgres | REFERENCES | t57
postgres | TRIGGER | t57
deneme3 | SELECT | t57
postgres | INSERT | t58
postgres | SELECT | t58
postgres | UPDATE | t58
postgres | DELETE | t58
postgres | TRUNCATE | t58
postgres | REFERENCES | t58
postgres | TRIGGER | t58
deneme3 | SELECT | t58
postgres | INSERT | foot1
postgres | SELECT | foot1
postgres | UPDATE | foot1
postgres | DELETE | foot1
postgres | TRUNCATE | foot1
postgres | REFERENCES | foot1
postgres | TRIGGER | foot1
deneme3 | SELECT | foot1
deneme2 | INSERT | foot5
deneme2 | SELECT | foot5
deneme2 | UPDATE | foot5
deneme2 | DELETE | foot5
deneme2 | TRUNCATE | foot5
deneme2 | REFERENCES | foot5
deneme2 | TRIGGER | foot5
deneme3 | SELECT | foot5
postgres | INSERT | foot2
postgres | SELECT | foot2
postgres | UPDATE | foot2
postgres | DELETE | foot2
postgres | TRUNCATE | foot2
postgres | REFERENCES | foot2
postgres | TRIGGER | foot2
deneme3 | SELECT | foot2
postgres | INSERT | foot3
postgres | SELECT | foot3
postgres | UPDATE | foot3
postgres | DELETE | foot3
postgres | TRUNCATE | foot3
postgres | REFERENCES | foot3
postgres | TRIGGER | foot3
deneme3 | SELECT | foot3
deneme2 | INSERT | foot6
deneme2 | SELECT | foot6
deneme2 | UPDATE | foot6
deneme2 | DELETE | foot6
deneme2 | TRUNCATE | foot6
deneme2 | REFERENCES | foot6
deneme2 | TRIGGER | foot6
deneme3 | SELECT | foot6
postgres | INSERT | t63
postgres | SELECT | t63
postgres | UPDATE | t63
postgres | DELETE | t63
postgres | TRUNCATE | t63
postgres | REFERENCES | t63
postgres | TRIGGER | t63
deneme3 | SELECT | t63
postgres | INSERT | t59
postgres | SELECT | t59
postgres | UPDATE | t59
postgres | DELETE | t59
postgres | TRUNCATE | t59
postgres | REFERENCES | t59
postgres | TRIGGER | t59
deneme3 | SELECT | t59
postgres | INSERT | t60
postgres | SELECT | t60
postgres | UPDATE | t60
postgres | DELETE | t60
postgres | TRUNCATE | t60
postgres | REFERENCES | t60
postgres | TRIGGER | t60
deneme3 | SELECT | t60
postgres | INSERT | t61
postgres | SELECT | t61
postgres | UPDATE | t61
postgres | DELETE | t61
postgres | TRUNCATE | t61
postgres | REFERENCES | t61
postgres | TRIGGER | t61
deneme3 | SELECT | t61
postgres | INSERT | t62
postgres | SELECT | t62
postgres | UPDATE | t62
postgres | DELETE | t62
postgres | TRUNCATE | t62
postgres | REFERENCES | t62
postgres | TRIGGER | t62
deneme3 | SELECT | t62
postgres | INSERT | t64
postgres | SELECT | t64
postgres | UPDATE | t64
postgres | DELETE | t64
postgres | TRUNCATE | t64
postgres | REFERENCES | t64
postgres | TRIGGER | t64
deneme3 | SELECT | t64
(175 rows)
THE DATABASE DIARY 