Upgrading a database is an important task. Being a DBA, you won't want your DB to be down for a long period of time which may be required to upgrade your database to a new major version. Many companies avoid upgrades because they cannot afford a downtime / maintenance window. And hence they continue struggling with the issues and bugs that are already resolved in the later versions and are unacquainted from the features introduced in the newer versions.
To avoid such down time and to be able enjoy the new versions, you can use EDB Postgres Replication Server (xDB). xDB can help users to upgrade to the latest major versions without downtime, by creating logical replication between two nodes with different major versions on same or different host machines.
The process involves installing EDB Postgres Replication server on your DB server host or on an isolated node connecting to the DB server. For more details on the installation of xDB please refer the document here.
After the installation is completed, you can follow the example steps below for setting up the source and destination databases as Publication and Subscription respectively.
In the test below, I am trying to upgrade my EPAS13 host to EPAS14.
Upgrading DB server using xDB.
edb=# select version();
version
PostgreSQL 13.6 (EnterpriseDB Advanced Server 13.6.10) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)
edb=# \dt
List of relations
Schema | Name | Type | Owner
public | tab1 | table | enterprisedb
public | tab2 | table | enterprisedb
vipul | tab3 | table | enterprisedb
(3 rows)
edb=# select count(*) from tab1;
count
1000
(1 row)
edb=# select count(*) from tab2;
count
1000
(1 row)
edb=# select count(*) from vipul.tab3;
count
1000
(1 row)
edb=# \ds
List of relations
Schema | Name | Type | Owner
public | serial | sequence | enterprisedb
(1 row)
edb=# \du
List of roles
Role name | Attributes | Member of
aq_administrator_role | No inheritance, Cannot login +| {}
| Profile default |
enterprisedb | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
| Profile default |
vipul | Profile default | {}
Dump the cluster structure using pg_dumpall utility with -s option.
$PGBIN/pg_dumpall -h <$PGHOST13> -p <$PGPORT> -s -f /tmp/structural.sql
On the target server, initialize a new server using initdb utility and restore the structural dump of the source server.
-bash-4.2$ ./psql -h <$PGHOST14> -p <$PGPORT> -d edb -f /tmp/structural.sql
...
...
edb=# select version();
version
PostgreSQL 14.2 (EnterpriseDB Advanced Server 14.2.1) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)
edb=# set search_path to 'public', 'vipul';
SET
edb=# \dt
List of relations
Schema | Name | Type | Owner
public | tab1 | table | enterprisedb
public | tab2 | table | enterprisedb
vipul | tab3 | table | enterprisedb
(3 rows)
edb=# select count(*) from tab1;
count
0
(1 row)
edb=# select count(*) from tab2;
count
0
(1 row)
edb=# select count(*) from vipul.tab3;
count
0
(1 row)
edb=# \ds
List of relations
Schema | Name | Type | Owner
public | serial | sequence | enterprisedb
(1 row)
edb=# \du
List of roles
Role name | Attributes | Member of
aq_administrator_role | No inheritance, Cannot login +| {}
| Profile default |
enterprisedb | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
| Profile default |
vipul | Profile default | {}
edb=# \q
In order to configure xDB for accepting the table structure present on the target database set offlineSnapshot
to true
in the xDB configuration file xdb_pubserver.conf.
[root@localhost ~]# java -jar /usr/edb/xdb/bin/edb-repcli.jar -version
Version: 7.5.0
[root@localhost ~]# echo edb > input.txt
[root@localhost ~]# java -jar /usr/edb/xdb/bin/edb-repcli.jar -encrypt -input input.txt -output output.txt
[root@localhost ~]# cat output.txt
SJ70z8Gk0zY=
[root@localhost ~]# cat pubsvrfile.prop
host=localhost
port=9051
user=admin
password=SJ70z8Gk0zY=
[root@localhost ~]# systemctl start edb-xdbpubserver.service
[root@localhost ~]# systemctl status edb-xdbpubserver.service
● edb-xdbpubserver.service - Publication Server Service script for Linux
Loaded: loaded (/usr/lib/systemd/system/edb-xdbpubserver.service; disabled; vendor preset: disabled)
Active: active (running) since Wed 2022-03-23 19:30:40 IST; 4s ago
Process: 111451 ExecStart=/bin/bash -c cd /usr/edb/xdb/bin; ./runPubServer.sh >> /var/log/edb/xdb/edb-xdbpubserver.log 2>&1 & (code=exited, status=0/SUCCESS)
Main PID: 111452 (bash)
Tasks: 17
CGroup: /system.slice/edb-xdbpubserver.service
├─111452 /bin/bash -c cd /usr/edb/xdb/bin; ./runPubServer.sh >> /var/log/edb/xdb/edb-xdbpubserver.log 2>&1 &
└─111481 /usr/bin/java -XX:-UsePerfData -Xms256m -Xmx1536m -XX:ErrorFile=/var/log/edb/xdb/pubserver_pid_%p.log -Djava.library.path=/usr/edb/xdb/bin -Djava.awt.headless=true -jar /usr/...
Mar 23 19:30:40 localhost.localdomain systemd[1]: Starting Publication Server Service script for Linux...
Mar 23 19:30:40 localhost.localdomain systemd[1]: Started Publication Server Service script for Linux.
[root@localhost ~]# java -jar /usr/edb/xdb/bin/edb-repcli.jar -addpubdb -repsvrfile ~/pubsvrfile.prop -dbtype enterprisedb -dbhost localhost -dbport 5444 -dbuser enterprisedb -dbpassword SJ70z8Gk0zY= -database edb
Adding publication database...
Publication database added successfully. Publication database id:1
edb=# select * from _edb_replicator_pub.xdb_pub_database ;
pub_db_id | db_host | db_port | db_user | db_password | db_name | db_type | url_options | rep_group | is_mdn | status | db_priority | changeset_log_mode | db_oid
1 | localhost | 5444 | enterprisedb | SJ70z8Gk0zY= | edb | 0 | | S | f | | 0 | T | 0
(1 row)
[root@localhost ~]# java -jar /usr/edb/xdb/bin/edb-repcli.jar -createpub pub_smr1 -repsvrfile ~/pubsvrfile.prop -pubdbid 1 -reptype t -tables public.tab1 public.tab2 vipul.tab3
Creating publication...
Tables:[[public.tab1, TABLE], [public.tab2, TABLE], [vipul.tab3, TABLE]]
Filter clause:[]
Publication created.
edb=# select * from _edb_replicator_pub.xdb_publications;
pub_id | pub_name | rep_type | pub_db_id
21 | pub_smr1 | T | 1
(1 row)
edb=# select * from _edb_replicator_pub.rrep_tables ;
table_id | schema_name | table_name | shadow_table_name | table_type
8 | public | tab1 | rrst_public_tab1 | T
9 | public | tab2 | rrst_public_tab2 | T
10 | vipul | tab3 | rrst_vipul_tab3 | T
(3 rows)
[root@localhost ~]# systemctl start edb-xdbsubserver.service
[root@localhost ~]# systemctl status edb-xdbsubserver.service
● edb-xdbsubserver.service - Subscription Server Service script for Linux
Loaded: loaded (/usr/lib/systemd/system/edb-xdbsubserver.service; disabled; vendor preset: disabled)
Active: active (running) since Wed 2022-03-23 19:39:00 IST; 7s ago
Process: 112435 ExecStart=/bin/bash -c cd /usr/edb/xdb/bin; ./runSubServer.sh >> /var/log/edb/xdb/edb-xdbsubserver.log 2>&1 & (code=exited, status=0/SUCCESS)
Main PID: 112436 (bash)
Tasks: 32
CGroup: /system.slice/edb-xdbsubserver.service
├─112436 /bin/bash -c cd /usr/edb/xdb/bin; ./runSubServer.sh >> /var/log/edb/xdb/edb-xdbsubserver.log 2>&1 &
└─112465 /usr/bin/java -XX:-UsePerfData -XX:ErrorFile=/var/log/edb/xdb/subserver_pid_%p.log -Djava.awt.headless=true -jar /usr/edb/xdb/bin/edb-repserver.jar subserver 9052
Mar 23 19:39:00 localhost.localdomain systemd[1]: Starting Subscription Server Service script for Linux...
Mar 23 19:39:00 localhost.localdomain systemd[1]: Started Subscription Server Service script for Linux.
[root@localhost ~]# java -jar /usr/edb/xdb/bin/edb-repcli.jar -addsubdb -repsvrfile ~/subsvrfile.prop -dbtype enterprisedb -dbhost localhost -dbport 5445 -dbuser enterprisedb -dbpassword SJ70z8Gk0zY= -database edb
Adding Subscription Database...
Subscription database added successfully. Subscription Database id:31
edb=# select * from _edb_replicator_sub.xdb_sub_database ;
sub_db_id | db_host | db_port | db_user | db_password | db_name | db_type | url_options
41 | localhost | 5445 | enterprisedb | SJ70z8Gk0zY= | edb | 0 |
(1 row)
[root@localhost ~]# java -jar /usr/edb/xdb/bin/edb-repcli.jar -createsub sub_smr1 -subsvrfile ~/subsvrfile.prop -subdbid 41 -pubsvrfile ~/pubsvrfile.prop -pubname pub_smr1
Creating subscription...
Subscription created successfully
edb=# select * from _edb_replicator_sub.xdb_subscriptions ;
sub_id | sub_name | sub_db_id | sub_server_id | sub_server_name | pub_id | pub_server_ip | pub_server_port | pub_rep_type | pub_db_type
61 | sub_smr1 | 41 | 71 | | 21 | localhost | 9051 | T | 0
(1 row)
[root@localhost ~]# java -jar /usr/edb/xdb/bin/edb-repcli.jar -dosnapshot sub_smr1 -repsvrfile ~/subsvrfile.prop
Performing snapshot...
Setting any pending transactions status to cancelled...
Running EnterpriseDB Migration Toolkit (Build 55.5.0) ...
Source database connectivity info...
conn =jdbc:edb://localhost:5444/edb?loginTimeout=60&connectTimeout=30&socketTimeout=900
user =enterprisedb
password=******
Target database connectivity info...
conn =jdbc:edb://localhost:5445/edb?loginTimeout=60&connectTimeout=30&socketTimeout=900
user =enterprisedb
password=******
Connecting with source EDB Postgres database server...
Connected to EnterpriseDB, version '13.6.10'
Connecting with target EDB Postgres database server...
Connected to EnterpriseDB, version '14.2.1'
Importing enterprisedb schema public...
Table List: 'tab1','tab2'
Evaluating tables for parallel data load...
Evaluating table public.tab1 for parallel data load...
Evaluating table public.tab2 for parallel data load...
Initializing Data Loader 1...
Initializing Data Loader 2...
Loading Table Data in 8 MB batches...
Dropping constraints & indexes on public.tab1 before data load...
Dropping Triggers on public.tab1 before data load...
Truncating table public.tab1 before data load...
Loading Table: public.tab1 ...
Dropping constraints & indexes on public.tab2 before data load...
Dropping Triggers on public.tab2 before data load...
Truncating table public.tab2 before data load...
Loading Table: public.tab2 ...
[tab1] Migrated 1000 rows.
[tab1] Table Data Load Summary: Total Time(s): 0.372 Total Rows: 1000 Total Size(KB): 15.521
[tab2] Migrated 1000 rows.
[tab2] Table Data Load Summary: Total Time(s): 0.235 Total Rows: 1000 Total Size(KB): 15.521
Creating constraints, indexes and triggers after data load...
Performing ANALYZE on EnterpriseDB database...
Data Load Summary: Total Time (sec): 0.607 Total Rows: 2000 Total Size(KB): 31.043
Schema public imported successfully.
Importing enterprisedb schema vipul...
Table List: 'tab3'
Evaluating tables for parallel data load...
Evaluating table vipul.tab3 for parallel data load...
Initializing Data Loader 1...
Loading Table Data in 8 MB batches...
Dropping constraints & indexes on vipul.tab3 before data load...
Dropping Triggers on vipul.tab3 before data load...
Truncating table vipul.tab3 before data load...
Loading Table: vipul.tab3 ...
[tab3] Migrated 1000 rows.
[tab3] Table Data Load Summary: Total Time(s): 0.175 Total Rows: 1000 Total Size(KB): 15.521
Creating constraints, indexes and triggers after data load...
Performing ANALYZE on EnterpriseDB database...
Data Load Summary: Total Time (sec): 0.782 Total Rows: 1000 Total Size(KB): 15.521
Schema vipul imported successfully.
Migration process completed successfully.
Total Elapsed Migration Time (sec): 2.605
Migration logs have been saved to /var/log/edb/xdb
******************** Migration Summary ********************
Tables: 3 out of 3
Total objects: 3
Successful count: 3
Failed count: 0
Invalid count: 0
*************************************************************
Performing post-Snapshot implicit Synchronize operation for target database localhost:5445/edb...
The post-Snapshot implicit Synchronize operation for target database localhost:5445/edb completed successfully.
Snapshot taken successfully.
edb=# select count(*) from tab1;
count
1000
(1 row)
edb=# select count(*) from tab2;
count
1000
(1 row)
edb=# select count(*) from vipul.tab3;
count
1000
(1 row)
Once the snapshot is completed you can switch the connections from the older version to the newer one. However, if you still need time to verify the data, to keep the state of this database in sync with the source server, you can schedule the synchronization job to keep updating the transaction to the target.
You can schedule the job using the below.
[root@localhost bin]# java -jar edb-repcli.jar -confschedule sub_smr1 -repsvrfile ~/subsvrfile.prop -jobtype t -realtime 60
Configuring scheduler ...
Job is successfully scheduled.
[root@localhost bin]# java -jar edb-repcli.jar -printschedule sub_smr1 -repsvrfile ~/subsvrfile.prop
Printing subscription schedule ...
Job type Synchronize
Scheduled time 2022-03-28 15:19:33
Previous fire time 2022-03-28 15:20:33
Next fire time 2022-03-28 15:21:33
Once the data is validated, you can run the below query to verify the publication and subscription are in sync, take a maintenance window (stop all user connections from source DB), perform a manual synchronization and switch the connections to the new servers. The old server can be later stopped/decommissioned.
WITH
src_db (current_xid_commit_timestamp) AS
(SELECT timestamp FROM pg_last_committed_xact()),
-- last replicated timestamp for target databases
target_db (target_db_id, last_repl_xid_timestamp) AS
(SELECT sub_db_id AS target_db_id, MAX(last_repl_xid_timestamp) AS last_repl_xid_timestamp
FROM _edb_replicator_pub.rrep_txset rt JOIN _edb_replicator_sub.xdb_subscriptions xs ON xs.sub_id=rt.sub_id WHERE status = 'C'
GROUP BY sub_db_id),
-- identity of target databases
target_db_identity (target_db_id, target_db_identity) AS
(SELECT sub_db_id, db_host || ':' || db_port || ':' || db_name
FROM _edb_replicator_sub.xdb_sub_database)
-- replication lag for each of the target databases
SELECT c.target_db_id, target_db_identity, AGE(current_xid_commit_timestamp, last_repl_xid_timestamp) repl_time_lag
FROM src_db a, target_db b, target_db_identity c WHERE b.target_db_id = c.target_db_id
ORDER BY target_db_id;