CENTOS Postgres pg_upgrade 9 to 11 – In Place – Link – No Copy – Limited Disk Space

I wanted to share my experience with upgrading postgres database server from major version 9 to 11. I am showing the steps that I took to get many servers in dev and production upgraded with limited disk space(not enough space to copy). I am hoping this will help with the problems I faced when testing this procedure. Using the –link parameter has drawbacks as noted in the documentation, however we perform full VM backups of each server so we can always restore from backup if the upgrade fails and we will not need to start the pg9.3 database again.

https://www.postgresql.org/docs/11/pgupgrade.html

-k
--link

use hard links instead of copying files to the new cluster
If you ran pg_upgrade with --link, the data files are shared between the old and new cluster. If you started the new cluster, the new server has written to those shared files and it is unsafe to use the old cluster.

Before we get started make a backup of the files pg_hba.conf and postgresql.conf for later use, you will need to use them later to reconstruct the pg11 configs.


[[email protected] ~]# cp /data1/data93/pg_hba.conf /root/
[[email protected] ~]# cp /data1/data93/postgresql.conf /root/

Use WGET to grab the RPMS from https://yum.postgresql.org

[[email protected] pg_11]# wget https://yum.postgresql.org/11/redhat/rhel-6-x86_64/postgresql11-11.2-2PGDG.rhel6.x86_64.rpm
[[email protected] pg_11]# wget https://yum.postgresql.org/11/redhat/rhel-6-x86_64/postgresql11-contrib-11.2-2PGDG.rhel6.x86_64.rpm
[[email protected] pg_11]# wget https://yum.postgresql.org/11/redhat/rhel-6-x86_64/postgresql11-debuginfo-11.2-2PGDG.rhel6.x86_64.rpm
[[email protected] pg_11]# wget https://yum.postgresql.org/11/redhat/rhel-6-x86_64/postgresql11-devel-11.2-2PGDG.rhel6.x86_64.rpm
[[email protected] pg_11]# wget https://yum.postgresql.org/11/redhat/rhel-6-x86_64/postgresql11-docs-11.2-2PGDG.rhel6.x86_64.rpm
[[email protected] pg_11]# wget https://yum.postgresql.org/11/redhat/rhel-6-x86_64/postgresql11-libs-11.2-2PGDG.rhel6.x86_64.rpm
[[email protected] pg_11]# wget https://yum.postgresql.org/11/redhat/rhel-6-x86_64/postgresql11-server-11.2-2PGDG.rhel6.x86_64.rpm

Install the RPMS for postgres11 that we just downloaded

[[email protected] pg_11]# rpm -ivh postgresql11-*
warning: postgresql11-11.2-2PGDG.rhel6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing... ########################################### [100%]
1:postgresql11-libs ########################################### [ 14%]
2:postgresql11 ########################################### [ 29%]
3:postgresql11-contrib ########################################### [ 43%]
4:postgresql11-devel ########################################### [ 57%]
5:postgresql11-server ########################################### [ 71%]
6:postgresql11-docs ########################################### [ 86%]
7:postgresql11-debuginfo ########################################### [100%]

We will create the data location for postgres11 where the files will be hardlinked and not copied. You can see the tablespace disk locations and the index locations from the pg9.3 install. Its important to create the new pg11 data directory on the same filesystem since we will be using the –link parameter and it uses hardlinks which will not traverse filesystems.
[[email protected] ~]# cd /data1/
[[email protected] data1]# ls -ltr
total 12
drwx------ 3 postgres postgres 4096 Apr 16 22:57 ts_index1
drwx------ 3 postgres postgres 4096 Apr 16 22:58 ts_data2
drwx------ 16 postgres postgres 4096 Apr 16 23:02 data93
[[email protected] data1]# mkdir data11
[[email protected] data1]# chown -R postgres:postgres data11/

We will need to init a postgres database in our new location on disk data11.

[[email protected] ~]# su - postgres
-bash-4.1$ /usr/pgsql-11/bin/initdb -D /data1/data11
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 database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /data1/data11 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

/usr/pgsql-11/bin/pg_ctl -D /data1/data11 -l logfile start

-bash-4.1$

Now we are ready to stop pg9.3 and check pg_upgrade compatibility. pg_upgrade ships with a –check argument that will check the compatibility of the clusters and be sure the upgrade will work before changing any files. Lets stop pg9.3 and run the pg_upgrade with the –check parameter.


[[email protected] ~]# /etc/init.d/postgresql-9.3 stop
Stopping postgresql-9.3 service: [ OK ]

[[email protected] ~]# su - postgres

-bash-4.1$ /usr/pgsql-11/bin/pg_upgrade --link --old-bindir=/usr/pgsql-9.3/bin/ --new-bindir=/usr/pgsql-11/bin/ --old-datadir=/data1/data93/ --new-datadir=/data1/data11/ --check
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 reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for invalid "unknown" user columns ok
Checking for hash indexes ok
Checking for roles starting with "pg_" ok
Checking for incompatible "line" data type ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok

*Clusters are compatible*

Ok checks have passed and the cluster versions are ready for upgrade, lets run this without the –check parameter and upgrade postgres.

[[email protected] ~]# su - postgres
-bash-4.1$ /usr/pgsql-11/bin/pg_upgrade --link --old-bindir=/usr/pgsql-9.3/bin/ --new-bindir=/usr/pgsql-11/bin/ --old-datadir=/data1/data93/ --new-datadir=/data1/data11/
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 reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for invalid "unknown" user columns ok
Checking for roles starting with "pg_" ok
Checking for incompatible "line" data type 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

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 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 /data1/data93/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

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
-bash-4.1$

OK the pg_upgrade code completed successfully and has generated 2 scripts. One to analyze the new pg11 cluster to get stats for the query planner and vacuum. The other to cleanup and remove the old pg9.3 locations on disk. Let’s start pg11, we will need to create an override file to tell pg11 where the data11 data lives, then we should be able to start postgres and check some things and verify our upgrade.

[[email protected] ~]# cd /etc/sysconfig/pgsql/
[[email protected] pgsql]# cp postgresql-9.3 postgresql-11
[[email protected] pgsql]# vim postgresql-11
[[email protected] pgsql]# cat postgresql-11
PGDATA=/data1/data11
PGLOG=/data1/data11/pgstartup.log

[[email protected] pgsql]# /etc/init.d/postgresql-11 start
Starting postgresql-11 service: [ OK ]

[[email protected] pgsql]# su - postgres
-bash-4.1$ ps -ef| grep postgres| head -n 1
postgres 31047 1 0 23:30 ? 00:00:00 /usr/pgsql-11/bin/postmaster -D /data1/data11
-bash-4.1$ psql
psql (11.2)
Type "help" for help.

postgres=# select spcname
,pg_tablespace_location(oid)
from pg_tablespace;
spcname | pg_tablespace_location
------------+------------------------
pg_default |
pg_global |
index1 | /data1/ts_index1
data2 | /data1/ts_data2
(4 rows)


OK we can see we have pg11 running and we can run the generated scripts to cleanup, but lets take a look at the data and index directories to see what the upgrade produced.

[[email protected] ~]# cd /data1/
[[email protected] data1]# ls
data11 data93 ts_data2 ts_index1
[[email protected] data1]# cd data11/
[[email protected] data11]# ls -l
total 132
drwx------ 5 postgres postgres 4096 Apr 16 23:19 base
-rw------- 1 postgres postgres 30 Apr 16 23:30 current_logfiles
drwx------ 2 postgres postgres 4096 Apr 16 23:32 global
drwx------ 2 postgres postgres 4096 Apr 16 23:10 log
drwx------ 2 postgres postgres 4096 Apr 16 23:09 pg_commit_ts
drwx------ 2 postgres postgres 4096 Apr 16 23:09 pg_dynshmem
-rw------- 1 postgres postgres 4513 Apr 16 23:09 pg_hba.conf
-rw------- 1 postgres postgres 1636 Apr 16 23:09 pg_ident.conf
drwx------ 4 postgres postgres 4096 Apr 16 23:35 pg_logical
drwx------ 4 postgres postgres 4096 Apr 16 23:19 pg_multixact
drwx------ 2 postgres postgres 4096 Apr 16 23:30 pg_notify
drwx------ 2 postgres postgres 4096 Apr 16 23:09 pg_replslot
drwx------ 2 postgres postgres 4096 Apr 16 23:09 pg_serial
drwx------ 2 postgres postgres 4096 Apr 16 23:09 pg_snapshots
-rw------- 1 postgres postgres 469 Apr 16 23:30 pgstartup.log
drwx------ 2 postgres postgres 4096 Apr 16 23:30 pg_stat
drwx------ 2 postgres postgres 4096 Apr 16 23:45 pg_stat_tmp
drwx------ 2 postgres postgres 4096 Apr 16 23:09 pg_subtrans
drwx------ 2 postgres postgres 4096 Apr 16 23:19 pg_tblspc
drwx------ 2 postgres postgres 4096 Apr 16 23:09 pg_twophase
-rw------- 1 postgres postgres 3 Apr 16 23:09 PG_VERSION
drwx------ 3 postgres postgres 4096 Apr 16 23:19 pg_wal
drwx------ 2 postgres postgres 4096 Apr 16 23:19 pg_xact
-rw------- 1 postgres postgres 88 Apr 16 23:09 postgresql.auto.conf
-rw------- 1 postgres postgres 23863 Apr 16 23:09 postgresql.conf
-rw------- 1 postgres postgres 48 Apr 16 23:30 postmaster.opts
-rw------- 1 postgres postgres 95 Apr 16 23:30 postmaster.pid
[[email protected] data11]# cd ../ts_index1/
[[email protected] ts_index1]# ls -l
total 8
drwx------ 2 postgres postgres 4096 Apr 16 23:19 PG_11_201809051
drwx------ 2 postgres postgres 4096 Apr 16 22:57 PG_9.3_201306121
[[email protected] ts_index1]# cd ../ts_data2/
You have mail in /var/spool/mail/root
[[email protected] ts_data2]# ls -l
total 8
drwx------ 2 postgres postgres 4096 Apr 16 23:19 PG_11_201809051
drwx------ 2 postgres postgres 4096 Apr 16 22:58 PG_9.3_201306121


We can view the shell scripts that pg_upgrade produced and cleanup the old pg9.3 references and run the analyze vacuums.

[[email protected] ~]# su - postgres
-bash-4.1$ ls
11 9.3 analyze_new_cluster.sh delete_old_cluster.sh
-bash-4.1$ cat delete_old_cluster.sh
#!/bin/sh

rm -rf '/data1/data93'
rm -rf '/data1/ts_index1/PG_9.3_201306121'
rm -rf '/data1/ts_data2/PG_9.3_201306121'
-bash-4.1$ cat analyze_new_cluster.sh
#!/bin/sh

echo 'This script will generate minimal optimizer statistics rapidly'
echo 'so your system is usable, and then gather statistics twice more'
echo 'with increasing accuracy. When it is done, your system will'
echo 'have the default level of optimizer statistics.'
echo

echo 'If you have used ALTER TABLE to modify the statistics target for'
echo 'any tables, you might want to remove them and restore them after'
echo 'running this script because they will delay fast statistics generation.'
echo

echo 'If you would like default statistics as quickly as possible, cancel'
echo 'this script and run:'
echo ' "/usr/pgsql-11/bin/vacuumdb" --all --analyze-only'
echo

"/usr/pgsql-11/bin/vacuumdb" --all --analyze-in-stages
echo

echo 'Done'

This looks good, lets execute them and cleanup any pg9.3 references as well as remove the pg9.3 rpms.

[[email protected] data1]# su - postgres
-bash-4.1$ ./delete_old_cluster.sh
-bash-4.1$ ./analyze_new_cluster.sh
This script will generate minimal optimizer statistics rapidly
so your system is usable, and then gather statistics twice more
with increasing accuracy. When it is done, your system will
have the default level of optimizer statistics.

If you have used ALTER TABLE to modify the statistics target for
any tables, you might want to remove them and restore them after
running this script because they will delay fast statistics generation.

If you would like default statistics as quickly as possible, cancel
this script and run:
"/usr/pgsql-11/bin/vacuumdb" --all --analyze-only

vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics

Done
-bash-4.1$

Remove the pg9.3 rpms and references, set the new data location in the .pgsql_profile.
[[email protected] ~]# yum remove postgresql93*
Loaded plugins: fastestmirror
Setting up Remove Process
Resolving Dependencies
--> Running transaction check
---> Package postgresql93.x86_64 0:9.3.24-1PGDG.rhel6 will be erased
---> Package postgresql93-contrib.x86_64 0:9.3.24-1PGDG.rhel6 will be erased
---> Package postgresql93-debuginfo.x86_64 0:9.3.24-1PGDG.rhel6 will be erased
---> Package postgresql93-devel.x86_64 0:9.3.24-1PGDG.rhel6 will be erased
---> Package postgresql93-docs.x86_64 0:9.3.24-1PGDG.rhel6 will be erased
---> Package postgresql93-libs.x86_64 0:9.3.24-1PGDG.rhel6 will be erased
---> Package postgresql93-server.x86_64 0:9.3.24-1PGDG.rhel6 will be erased
--> Finished Dependency Resolution

Dependencies Resolved

===================================================================================================================================================================================================================
Package Arch Version Repository Size
===================================================================================================================================================================================================================
Removing:
postgresql93 x86_64 9.3.24-1PGDG.rhel6 @affinity6-prod-db 5.3 M
postgresql93-contrib x86_64 9.3.24-1PGDG.rhel6 @affinity6-prod-db 1.7 M
postgresql93-debuginfo x86_64 9.3.24-1PGDG.rhel6 @affinity6-prod-db 28 M
postgresql93-devel x86_64 9.3.24-1PGDG.rhel6 @affinity6-prod-db 6.8 M
postgresql93-docs x86_64 9.3.24-1PGDG.rhel6 @affinity6-prod-db 31 M
postgresql93-libs x86_64 9.3.24-1PGDG.rhel6 @affinity6-prod-db 632 k
postgresql93-server x86_64 9.3.24-1PGDG.rhel6 @affinity6-prod-db 16 M

Transaction Summary
===================================================================================================================================================================================================================
Remove 7 Package(s)

Installed size: 89 M
Is this ok [y/N]: y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Warning: RPMDB altered outside of yum.
Erasing : postgresql93-debuginfo-9.3.24-1PGDG.rhel6.x86_64 1/7
Erasing : postgresql93-devel-9.3.24-1PGDG.rhel6.x86_64 2/7
Erasing : postgresql93-server-9.3.24-1PGDG.rhel6.x86_64 3/7
Erasing : postgresql93-contrib-9.3.24-1PGDG.rhel6.x86_64 4/7
Erasing : postgresql93-9.3.24-1PGDG.rhel6.x86_64 5/7
Erasing : postgresql93-libs-9.3.24-1PGDG.rhel6.x86_64 6/7
Erasing : postgresql93-docs-9.3.24-1PGDG.rhel6.x86_64 7/7
Verifying : postgresql93-debuginfo-9.3.24-1PGDG.rhel6.x86_64 1/7
Verifying : postgresql93-9.3.24-1PGDG.rhel6.x86_64 2/7
Verifying : postgresql93-docs-9.3.24-1PGDG.rhel6.x86_64 3/7
Verifying : postgresql93-contrib-9.3.24-1PGDG.rhel6.x86_64 4/7
Verifying : postgresql93-server-9.3.24-1PGDG.rhel6.x86_64 5/7
Verifying : postgresql93-devel-9.3.24-1PGDG.rhel6.x86_64 6/7
Verifying : postgresql93-libs-9.3.24-1PGDG.rhel6.x86_64 7/7

Removed:
postgresql93.x86_64 0:9.3.24-1PGDG.rhel6 postgresql93-contrib.x86_64 0:9.3.24-1PGDG.rhel6 postgresql93-debuginfo.x86_64 0:9.3.24-1PGDG.rhel6 postgresql93-devel.x86_64 0:9.3.24-1PGDG.rhel6
postgresql93-docs.x86_64 0:9.3.24-1PGDG.rhel6 postgresql93-libs.x86_64 0:9.3.24-1PGDG.rhel6 postgresql93-server.x86_64 0:9.3.24-1PGDG.rhel6

Complete!
[[email protected] ~]#

[[email protected] ~]# cd /etc/sysconfig/pgsql/
You have new mail in /var/spool/mail/root
[[email protected] pgsql]# ls
postgresql-11 postgresql-9.3
[[email protected] pgsql]# rm -f postgresql-9.3
[[email protected] pgsql]# su - postgres
-bash-4.1$ ls
11 9.3 analyze_new_cluster.sh delete_old_cluster.sh
-bash-4.1$ rm -rf 9.3

You can now view the pg_hba.conf and postgresql.conf you saved in /root and add whats needed to the new pg11 configs.

That’s it!!

SINOPIA NPM allow connections to GITHUB as well as the NPM registry

SINOPIA LINK HERE
We use SINOPIA as a proxy on our internal network behind the firewall to allow users to install NODE packages without an internet connection. We basically run sinopia on a machine that has access to the internet and the clients point to the server to install packages that are not locally available. We have been running into issues where installs that needed access to github would fail with something like this:

As you can see, we are getting choked at:

To get around this we need to change the config.yml on the server to allow proxies to github, here is the final configuration. Hope this helps other users as we had a fun time trying to figure it out. Pay attention to the uplinks section and the proxy requests where github is defined.

PSQL Connect To AWS Redshift From Windows 10 PowerShell

Coming from a completely Linux background, I was tasked with connecting to a aws redshift cluster or a postgres cluster via Windows powershell and PSQL. I knew it was possible and searching the internet came up with CMD prompt solutions, when I attempted via powershell, I was faced with the following error:

Turns out a colleague of mine and I figured out you will need to set the variable PGCLIENTENCODING via the powershell command line. This was expected but we could not nail down the syntax, we found it.

Once this is set, you can connect to PG as normal.

Python Generator Find Files With Wildcard

This is a neat way to generate file names in a directory that match a specific pattern, I use this to generate a list of files exported out of hive to load into S3.

Nagios Python Plugin Check If File Is Stale

Wrote this simple plugin to check if a log file was stale on a server using nagios and nrpe. This plugin checks multiple files with the app. naming convention.

POSTGRES – Top 100 Tables In Tablespace

I had a situation where I needed to find the top 100 largest tables in a certain tablespace on a postgres 9 database, in my case we archive tables into an archive1 tablespace. This query will find all the largest relations in the archive1 tablespace. Its important to swap out ‘archive1’ with whatever tablespace you are trying to list.

Hope this helps you out, took some time to get it to work.

Nagios Check Postgres Table Date Column Against now()

I had a situation where a daily sync of a table from one database to another was failing. This table was updated daily so the query should return something like this when it was synced correctly:

I use Nagios very heavily and I setup a custom plugin to check the query’s date against today’s date, this should warn or critical based on user supplied arguments. Here is what a failure looks like when running from the nagios servers command line. This worked well at alerting me when the sync failed, this was integrated into the nagios subsystem and emails and slack alerts are generated as expected.

Nagios Python Plugin Check Lock File Exists

Code Highlighting Test

I just installed the crayon code highlight plugin from the link below, man you got to give credit where credit is due. This plugin was simple to install on my site and it has endless code highlighting capabilities.
Crayon Highlight
OMG: This is some pretty code highlighting if I must say so myself. Have a look:

PYTHON:

PHP:

C++:

BASH:

PERL:

JAVA:

CYGWIN – clear.exe from scratch C Program

Hello all,

TL;DR – I wrote a C program to use as clear.exe on cygwin, I know about CTRL-L but I wanted a binary for scripts, and I am used to typing clear at a terminal

I was recently enforced to use Windows as my daily computer at work for all types of compliance PCI reasons, reasons that I do not wish to dive into. However I am now on Windows, I spend most of my day in a BASH shell on a remote system, so it’s not that bad. However traversing around Windows with CMD or PS sucked. I was introduced to cygwin and I was really impressed, I was able to use a ton of tools that I was already familiar with on the Linux CLI. Native SSH,SCP were huge for me at this time, also the ability to customize the colors with the TTY settings was awesome.

One issue I ran into was I liked to use the clear command in my scripts and from the CLI when I want a fresh terminal. Well, I figured I could just re launch the cygwin installer and search for clear.exe and install it. This was not the case, unfortunately I was instructed to install ncurses and the associated libraries. I followed these recommendations and never got clear.exe to work.

So I did what any computer scientist would do, research how to write a C program on this and compile it and copy it to my $PATH and wouldn’t you know it works!!.

So Here it is in all it’s glory. clear.exe for Windows and CYGWIN:

### Clear no workie ###

Ok so we can see the clear program was not working in the above example. So I wrote the following lines of C code below and compiled them.

OK, cool, now you will need to get GCC on your Windows machine from the CYGWIN installer, so just re run the installer and search GCC, you can install it from there like so.

View post on imgur.com

Ok now for the awesome stuff, it’s time to compile this with GCC and create a new clear.exe binary for Windows and CYGWIN.

Now that we see we can use the new executable to clear out cygwin shell, let’s copy it somewhere so we can just type “clear” to get what we want.

And there you have it, have fun!!!

Jason