Scribd
Upload
28 views6 pages

Upgrade PostgreSQL 9.5 to 16 on Linux

PostgreSQL

Uploaded by

egfoundationindia
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
28 views6 pages

Upgrade PostgreSQL 9.5 to 16 on Linux

PostgreSQL

Uploaded by

egfoundationindia
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Upgrade Streaming Replication Setup using pg_upgrade

OS:Redhat Linux

OS Version:9

Postgresql old version:9.5

Postgresql new version:16

Old Cluster: /app/data

New Cluster: /var/lib/pgsql/16/data

Primary server:[Link]

Standby server:[Link]

Step1) Collect database tables, tablespace and users, etc.. list

Step2) move the old cluster.

mv /usr/local/pgsql /usr/local/pgsql_old

Step3) Install the new PostgreSQL

sudo dnf install -y [Link]


x86_64/[Link]

sudo dnf -qy module disable postgresql

sudo dnf install -y postgresql16-server

Step4) Initialize the new PostgreSQL cluster

sudo /usr/pgsql-16/bin/postgresql-16-setup initdb

Step5) Install contrib extension.

#If you are using extensions install postgresql16-contrib for extension updates
sudo dnf install -y postgresql16-contrib

Step6) Stop both servers

pg_ctl -D /var/lib/pgsql/16/data stop --->new cluster

pg_ctl -D /app/data stop--->old cluster

Note: Streaming replication and log-shipping standby servers must be running during this
shutdown, so they receive all changes.

Step7)Run pg_upgrade

#Always run the pg_upgrade binary of the new server, not the old one. pg_upgrade requires the
specification of the old and new cluster's data and executable (bin) directories.

/usr/pgsql-16/bin/pg_upgrade --old-bindir "old-bindir_path" --new-bindir "new-bindir_path" --


old-datadir "old-datadir_path" --new-datadir "new-datadir_path"

#perform only the checks(option -c)

/usr/pgsql-16/bin/pg_upgrade -b /usr/local/pgsql_old/bin -B /usr/pgsql-16/bin -d /app/data -D


/var/lib/pgsql/16/data -c

#use hard links(option -k) instead of copying files to the new cluster

/usr/pgsql-16/bin/pg_upgrade -b /usr/local/pgsql_old/bin -B /usr/pgsql-16/bin -d /app/data -D


/var/lib/pgsql/16/data -k
Step8) Upgrade streaming replication and log-shipping standby servers

#Install the new PostgreSQL binaries on standby servers

sudo dnf install -y [Link]


x86_64/[Link]

sudo dnf -qy module disable postgresql

sudo dnf install -y postgresql16-server

#Make sure the new standby data directories do not exist

Make sure the new standby data directories do not exist or are empty. If initdb was run, delete
the standby servers' new data directories.

rm -rf recursive /var/lib/pgsql/16/data/*

#Install contrib extension

sudo dnf install -y postgresql16-contrib

#Stop standby servers

pg_ctl -D /app/data stop--->old cluster

#Run rsync

When using link mode, standby server can be quickly upgraded using rsync ,run this on the
primary for standby server:
rsync --archive --delete --hard-links --size-only --no-inc-recursive /app/data
/var/lib/pgsql/16/data

rsync --archive --delete --hard-links --size-only --no-inc-recursive /var/lib/pgsql/16/data


postgres@[Link]:/var/lib/pgsql/16

#If you have tablespaces, you will need to run a similar rsync command for each
tablespace directory

rsync --archive --delete --hard-links --size-only --no-inc-recursive /app/tbl_space1


postgres@[Link]:/app

rsync --archive --delete --hard-links --size-only --no-inc-recursive /app/tbl_space2


postgres@[Link]:/app

Step9) Configure streaming replication and log-shipping standby server

#Create a file [Link] in the standby's cluster data directory

cd /var/lib/pgsql/16/data

touch [Link]

#Edit [Link]

vim /var/lib/pgsql/16/data/[Link]

listen_addresses = '*'

port = 5432

primary_conninfo = 'host=[Link] port=5432 user=repuser password=reppass@123


options=''-c wal_sender_timeout=5000'''

hot_standby = on

restore_command = 'rsync -a postgres@[Link]:/mnt/server/archivedir/%f %p'

save&exit

Step10) Start the new server.

#Start the new upgraded primary server

sudo systemctl enable postgresql-16

sudo systemctl start postgresql-16

#Check the status

sudo systemctl status postgresql-16

Step11) Configure primary server for streaming replication and log-shipping

#Edit [Link] file

vim /var/lib/pgsql/16/data/[Link]
listen_addresses = '*'

port = 5432

wal_level = replica

archive_mode = on

archive_command = 'rsync -a %p /mnt/server/archivedir/%f && rsync -a %p


postgres@[Link]:/mnt/server/archivedir/%f'

save&exit

#Edit pg_hba.conf file

vim /var/lib/pgsql/16/data/pg_hba.conf

# replication privilege.

host replication repuser [Link]/32 md5

save&exit

#Restart postgresql server

sudo systemctl restart postgresql-16

Step12) Start rsync'ed standby server.

#Start the postgresql

sudo systemctl start postgresql-16

#Check the status

sudo systemctl status postgresql-16

Step13) Verify the replication status.

#on primary database instance

select * from pg_stat_replication;


#on Standby database instance

select * from pg_stat_wal_receiver ;

Step14) Delete old cluster on both primary and standby.

rm -rf '/app/data'

rm -rf '/app/tbl_space1/PG_9.5_201510051'

rm -rf '/app/tbl_space2/PG_9.5_201510051'

Step15) Check database tables, tablespace, and users, etc. list

Common questions

Powered by AI

In the postgresql.conf file, changes include setting listen_addresses to '*', port to 5432, enabling archive_mode, specifying an archive_command for rsync operations, and setting wal_level to replica. In pg_hba.conf, replication privileges must be configured to allow access for the replication user from the standby server's IP address, requiring an md5 authentication method. These changes ensure the system is prepared for secure and consistent streaming replication .

The process involves several key steps: collecting information about existing database tables, tablespaces, and users; moving the old PostgreSQL cluster directory; installing the new PostgreSQL version; initializing the new database cluster; stopping both the old and new clusters while ensuring streaming replication servers are running; running pg_upgrade with checks and using hard links to upgrade the data; upgrading the standby servers; configuring the streaming replication and log-shipping on both primary and standby servers; starting the upgraded PostgreSQL services and verifying replication status; and finally cleaning up by deleting the old cluster directories .

Stopping both the old and new PostgreSQL clusters while keeping streaming replication servers running is crucial to ensure that all changes are captured and transferred from the primary to the standby, maintaining synchronicity between their states. This practice prevents data discrepancies during the transition and guarantees that when operations resume post-upgrade, both servers are starting from a consistent and synchronized data state .

Deleting the old cluster files is important to free up storage space and avoid confusion or unintended connection to the obsolete data. Once the new PostgreSQL setup is confirmed operational and stable, retaining old data clusters could pose a risk of data inconsistency, unnecessary resource usage, and potential security vulnerabilities .

Using hard links instead of copying files during an upgrade with pg_upgrade is recommended because it improves performance by avoiding the need to duplicate data, thereby reducing the time taken for the upgrade and the additional storage required .

When installing new PostgreSQL binaries on standby servers, it is critical to ensure that any existing data directories related to the standby setup are removed or empty. Running the initdb process or deploying new files to existing non-empty directories could lead to data inconsistency or corruption. Therefore, prior to configuring the new setup, execute a recursive delete command on the new data directories to guarantee that they are in the proper initial state for receiving updated data from the primary server .

Rsync is used to quickly upgrade the standby server by synchronizing the new data directories from the primary server. It uses options that ensure efficient and consistent file transfer such as --archive, --delete, --hard-links, and --no-inc-recursive. This approach minimizes downtime and ensures that the standby server's data directories are accurately updated to reflect the state of the primary server .

Enabling archive_mode in the postgresql.conf file allows the system to archive write-ahead logs (WAL) which can then be used for log-shipping. This is crucial for maintaining consistent replication states between the primary and standby servers and ensuring that all transactions are securely documented and can be reflected on the standby in case of a failover .

The status of PostgreSQL replication can be verified by executing queries on the primary database instance to check the data in pg_stat_replication, and on the standby database instance to view pg_stat_wal_receiver. These queries provide insight into the current state of replication, including the connection status and lag between the primary and standby servers .

Step 9 is crucial as it involves configuring the new standby server for streaming replication, ensuring it can effectively communicate with the primary server. This includes touching the standby.signal file to initiate standby behavior, editing postgresql.conf to specify connection details, and setting up the restore_command to facilitate communication during replication. These steps ensure the standby server is correctly aligned with the operational requirements of the upgraded PostgreSQL environment, allowing it to seamlessly take over in case of primary server failure .

You might also like