Tag Archives: Postgresql

Upgrading to PostgreSQL 10 on Centos 7

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 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 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 automtically

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!