Re: Upgrade 9.5 cluster with non default data directory with checksum

[Debraj Manna <subharaj.manna@xxxxxxxxx>]

The issue has been resolved as discussed below

https://dba.stackexchange.com/q/190901/138324

Sent from GMail on Android

On Nov 15, 2017 5:26 PM, "Debraj Manna" <subharaj.manna@xxxxxxxxx> wrote:

Hi

I have added listen_addresses = '*' in my postgresql.conf.

On Wed, Nov 15, 2017 at 5:20 PM, Joan Luc Labòrda <laborde@xxxxxxxxxxxxxxxxxxxxx> wrote:

Hi

Le 15/11/2017 à 07:54, Debraj Manna a écrit :

I have made the changes as mentioned here. 

Now I am getting error with message 

connection to database failed: could not translate host name "." to address: No address associated with hostname
Failure, exiting

This seems to be a problem with network name resolution. Usely on some systems "." stands for "localhost" so maybe you could check in your postgresql.conf how the connections are specified

Can someone let me know when can this error come? What does it mean?

On Tue, Nov 14, 2017 at 11:14 PM, Debraj Manna <subharaj.manna@xxxxxxxxx> wrote:

At a high level I am following the below steps

Install postgres10

sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main"

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

sudo apt-get update

sudo apt-get install postgresql-10

Stop postgresql

sudo service postgresql stop

Create new data directory

sudo -H -u postgres mkdir -p /var/lib/postgresql/10/data/postgresql0

Enable Checksum on the newly created directory

sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/data/postgresql0 initdb -o '--data-checksums'

Then trying to check for upgrade viability with the below command 

sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_upgrade \

   -b /usr/lib/postgresql/9.5/bin \

   -B /usr/lib/postgresql/10/bin \

   -d /var/lib/postgresql/data/postgresql0 \

   -D /var/lib/postgresql/10/data/postgresql0 \

   -o ' -c config_file=/etc/postgresql/9.5/main/postgresql.conf' \

   -O ' -c config_file=/etc/postgresql/10/main/postgresql.conf' --check 

It is failing with the below error in pg_upgrade_server.log

command: "/usr/lib/postgresql/9.5/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/data/postgresql0" -o "-p 50432 -b  -c config_file=/etc/postgresql/9.5/main/postgresql.conf -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/tmp'" start >> "pg_upgrade_server.log" 2>&1

waiting for server to start....2017-11-14 15:27:31 UTC [16239-1] LOG:  database system was shut down at 2017-11-14 15:16:43 UTC

2017-11-14 15:27:31 UTC [16239-2] LOG:  MultiXact member wraparound protections are now enabled

2017-11-14 15:27:31 UTC [16238-1] LOG:  database system is ready to accept connections

........................................................... stopped waiting

pg_ctl: could not start server

I have attached the full installation log again.

On Tue, Nov 14, 2017 at 9:13 PM, Debraj Manna <subharaj.manna@xxxxxxxxx> wrote:

Thanks Keith for your prompt reply. 

But on trying to execute the below command

sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_upgrade \

   -b /usr/lib/postgresql/9.5/bin \

   -B /usr/lib/postgresql/10/bin \

   -d /var/lib/postgresql/data/postgresql0 \

   -D /var/lib/postgresql/10/data/postgresql0 \

   -o ' -c config_file=/etc/postgresql/9.5/main/postgresql.conf' \

   -O ' -c config_file=/etc/postgresql/10/main/postgresql.conf' --check 

It is failing with the below error in pg_upgrade_server.log

command: "/usr/lib/postgresql/9.5/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/data/postgresql0" -o "-p 50432 -b  -c config_file=/etc/postgresql/9.5/main/postgresql.conf -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/tmp'" start >> "pg_upgrade_server.log" 2>&1

waiting for server to start....2017-11-14 15:27:31 UTC [16239-1] LOG:  database system was shut down at 2017-11-14 15:16:43 UTC

2017-11-14 15:27:31 UTC [16239-2] LOG:  MultiXact member wraparound protections are now enabled

2017-11-14 15:27:31 UTC [16238-1] LOG:  database system is ready to accept connections

........................................................... stopped waiting

pg_ctl: could not start server

I have attached the entire installation log.

Can you let me know what I am doing wrong? Looks like some issue with the port conflict. I have not modified any configuration during the installation 9.5 is running on 5432 and 10 on 5433 as per postgresql.conf.

On Tue, Nov 14, 2017 at 8:29 PM, Keith <keith@xxxxxxxxxxx> wrote:

I highly advise not using pg_upgradecluster. It's a wrapper script for debian based systems and it obfuscates way too much of the upgrade process as you're seeing yourself with this question. You can use pg_createcluster to create the upgrade target version. Copy your config files over as needed but then just use pg_upgrade itself so it's clear what is getting upgraded and you'll get much better feedback about the process itself if there is a problem.

Keith

On Tue, Nov 14, 2017 at 9:28 AM, Debraj Manna <subharaj.manna@xxxxxxxxx> wrote:

Hi

I am trying to upgrade 9.5.4 cluster with data directory as /var/lib/postgresql/data/postgresql0 and have checksum enabled

Can someone let me know is it possible to upgrade this cluster to latest 10.1 using pg_upgradecluster ? 

In pg_upgradecluster man page I am not seeing any option to pass the data directory for the old cluster and also to pass the checksum enabled option?

Thanks,

Debraj

-- 
Joan Luc Labòrda 05.5684.3013
laborde@xxxxxxxxxxxxxxxxxxxxx
jean-luc.laborde@xxxxxxxxxxxxx