Tag Archives: Postgresql

Upgrading to PostgreSQL 11 on Centos 7

Since my previous article Upgrading to PostgreSQL 10 on Centos 7 was so popular, I though I’d do a follow-up for anyone looking to upgrade a very simply configured PostgreSQL 10 server to PostgreSQL 11 on Centos 7.

First, and this goes without saying, backup your server!

Install the repo RPM for PosgresSQL 10
sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Install PosgreSQL 11
sudo yum install postgresql11-server
Extensions

If you’re using extensions like pg_crypto, you will also need the postgresql11-contrib package

sudo yum install postgresql11-contrib
Stop Postgresql 10 and Postgresql 11
sudo systemctl stop postgresql-10.service && sudo systemctl stop postgresql-11.service
Initialize the PostgreSQL 11 database
sudo su postgres
cd ~/
/usr/pgsql-11/bin/initdb -D /var/lib/pgsql/11/data/
Migrate your database from the 10.x version to 11.x
/usr/pgsql-11/bin/pg_upgrade --old-datadir /var/lib/pgsql/10/data/ --new-datadir /var/lib/pgsql/11/data/ --old-bindir /usr/pgsql-10/bin/ --new-bindir /usr/pgsql-11/bin/
Edit configuration files

Make any necessary changes to postgresql.conf . I’d recommend making the changes to the new version rather than copying over postgresql.conf from 10.

You can view your 10 configuration with:

nano /var/lib/pgsql/10/data/postgresql.conf

You can make your changes to the 11 configuration with:

nano /var/lib/pgsql/11/data/postgresql.conf

If you need to connect from other servers, make sure to change:

#listen_addresses = 'localhost'

to (apostrophes may not survive copy/paste, may want to hand enter)

listen_addresses = '*'

Now do the same with pg_hba.conf

View the old configuration

nano /var/lib/pgsql/10/data/pg_hba.conf

Edit the new configuration

nano /var/lib/pgsql/11/data/pg_hba.conf
Start the server
systemctl start postgresql-11.service
Analyze and optimize the new cluster
./analyze_new_cluster.sh
Enable the PostgreSQL 11 Service (to start automatically)
systemctl enable postgresql-11
Remove PostgreSQL 10 and its data (if so desired)
./delete_old_cluster.sh
exit
sudo yum remove postgresql10-server

That should do it!

Upgrading to PostgreSQL 10 on Centos 7

(An updated version of this post for upgrading to PostgreSQL 11 is available here)

Here’s a quick rundown on upgrading a very simply configured PostgreSQL 9.x server to PostgreSQL 10 running on Centos 7.

First, and this goes without saying, backup your server!

In these examples, I’m using upgrading from PostgreSQL 9.5. If you’re upgrading from a different version, just replace 9.5 and 95 wherever you see it with your appropriate version number.

Install the repo RPM for PosgresSQL 10

sudo yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-1.noarch.rpm

Install PosgreSQL 10

sudo yum install postgresql10-server

Stop Postgresql 9.5 and Postgresql 10

sudo systemctl stop postgresql-9.5.service && sudo systemctl stop postgresql-10.service

Initialize the PostgreSQL 10 database

sudo su postgres
cd ~/
/usr/pgsql-10/bin/initdb -D /var/lib/pgsql/10/data/

Migrate your database from the 9.x version to 10

/usr/pgsql-10/bin/pg_upgrade --old-datadir /var/lib/pgsql/9.5/data/ --new-datadir /var/lib/pgsql/10/data/ --old-bindir /usr/pgsql-9.5/bin/ --new-bindir /usr/pgsql-10/bin/

Make any necessary changes to postgresql.conf . I’d recommend making the changes to the new version rather than copying over postgresql.conf from 9.5, since there are a bunch of new options in the PostreSQL 10 version of the file.

You can view your 9.5 configuration with:

nano /var/lib/pgsql/9.5/data/postgresql.conf

You can make your changes to the 10 configuration with:

nano /var/lib/pgsql/10/data/postgresql.conf

If you need to connect from other servers, make sure to change:

#listen_addresses = 'localhost'

to (apostrophes may not survive copy/paste, may want to hand enter)

listen_addresses = '*'

(or whatever is appropriate for you)

Now do the same with pg_hba.conf

View the old configuration

nano /var/lib/pgsql/9.5/data/pg_hba.conf

Edit the new configuration

nano /var/lib/pgsql/10/data/pg_hba.conf

Start the server

systemctl start postgresql-10.service

Analyze and optimize the new cluster

./analyze_new_cluster.sh

If everything is working, set the PostgreSQL 10 service to start automatically

systemctl enable postgresql-10

If you wish to remove PostgreSQL 9.x and its data

./delete_old_cluster.sh
exit
sudo yum remove postgresql95-server
sudo yum remove pgdg-centos95

That should do it!

NuGet Is Just Better

I was working on getting Postgresql, Visual Studio Remote Debugger, and PHP running on Server 2012 R2 so that I can up my debugging-fu, rather than just relying on Console.WriteLine.

Ran into some DLL hell trying to get npgsql working. I saw NuGet mentioned while Binging for solutions, so I figured I’d give it a try. Where has this been all my coding life?

A few clicks, and remote debugging is up and running. I even copied the compiled files over to my production box, and everything is working fine in Centos on Mono as well. What a great way to spend a Friday morning off!