Re: Switching from 9.1 to 9.5 on Ubuntu 16.04 - Mailing list pgsql-novice

Hi Mike,

please keep the mailing list posted, so maybe other people can help you too or get help from your problem.

That was finger trouble - sorry.

if your config files have errors, your server will refuse to start. You can read in the logfile what is happening and report it to us in case should you need help.

I tried to get everything working in a dry run but failed with frustrating mistakes. I decided to get the new server running on port 5435 and was somewhat successful but no cigar. I needed the ISP to open that port so I could manage the change externally from PGAdmin but it was going to take him all day.

In the end I couldn't stand it any more and announced an unscheduled maintenance with an opportunity for the users to veto. I left them with readonly access and voluntary abstaining from writing. Otherwise I would have had to take down two websites for the duration.

It is all working now - but with wrinkles. This is what I did ...

Dumped 2 databases

Swapped the port numbers so 9.1 was on port 5431 and 9.5 was on 5432

Restarted Postgres

I couldn't get PGAdmin4 to cooperate so I reverted to an old laptop with PGAdmin3 and discovered I needed to set the postgres password which I did via SSH on the production server using psql \password postgres

Tried creating my own miked user in psql CREATE ROLE 'miked' WITH SUPERUSER PASSWORD 'whatever' but that failed

Back in PGAdmin3 successfully created role miked as superuser

Back on the old laptop moved the dump files to within reach and loaded them both up on the 9.5 server using psql - successfully.

Did some sanity checks on the data (checked some session records etc) and proved everything is working in both databases and websites are working ok. Handed it all back to the users.

Swapped laptops and tried PGAdmin4 but got a "procpid" error connecting which went away after reloading the configuration and refreshing the page

The wrinkle is that PGAdmin4 is showing an extra database which I tried to drop like so ...

2018-08-21 16:12:23 AEST [5247-1] miked@postgres ERROR:  cannot drop a template database
2018-08-21 16:12:23 AEST [5247-2] miked@postgres STATEMENT:  DROP DATABASE template0;
2018-08-21 16:12:23 AEST [5271-1] miked@template0 FATAL:  database "template0" is not currently accepting connections
2018-08-21 16:50:02 AEST [5658-1] miked@1650 FATAL:  database "1650" does not exist

I have no idea what database 1650 might be.

postgres  |  postgres |  default administrative connection database

ssds      |  miked    |

template0 |  postgres |  unmodifiable empty database

train     |  miked    |

This does not show in PGAdmin3 on the other laptop

All in all I'm relieved. I could always have reverted to 9.1 and I'm glad I don't have to.

I just need to delete 9.1 now and maybe learn to live with template0 database showing in PGAdmin4

Thanks Fabio

Mike

The fact that when you start postgresql it does not return errors, is suspicious to me, therefore it might be that everything is ok, but you cannot connect to your instance.

Maybe answering to the following, will give to us a better picture:

- do you see postgres running on the processes list of the system after you start 9.5 instance?

- do you have special needs to run 'createdb'? because a pg_dumpall might turn out handy, depending from your situation (instead of pg_dump)

- can you connect to your db instance running 'psql' before and after the operations? (as in: before stopping 9.1 - after starting 9.1 - after starting 9.5 )

- I think your previous point '3. Adjust (9.1) postgresql.conf port entry from 5432 to 5431' is not needed in your case, since after the dump you do not need 9.1 any longer. Or there is anything I am missing?

hope it helps,

fabio pardi

On 20/08/18 15:43, Mike Dewhirst wrote:

Fabio

Yes. I should have said I have been testing on a duplicate VM copy of the system. As soon as I can do it successfully I'll schedule the upgrade and announce downtime for the process.

My problem is that I cannot switch the 9.1 server off and the 9.5 server on!

I'm doing something wrong!

M

Connected by Motorola

Fabio Pardi <f.pardi@portavita.eu> wrote:

Hi Mike,

best (and must) for you and your customers would be if you could test on a non production env. (and obscure IP addresses from your messages)

In order to do not lose data I would do the following steps instead:

1. Block access to database so that nobody except you can read or write.

2. dump db 9.1

3. stop db 9.1

4 start db 9.5

5. import dump to 9.5

6. restore access

Note that you might need to change your postgresql.conf file because of the introduction of 'max_wal_size' from 9.5 on.

Also note that in your proposed procedure here below, you might lose new data inserted between 1 and 2 because the dump is a snapshot taken at the time you initiate the process: new data that comes in after that, is not included in it.

Also something to keep in mind is the size of the dump. if is very big, you might want to go through the pg_ugrade procedure in order to keep downtime window smaller.

regards,

fabio pardi

On 20/08/18 13:21, Mike Dewhirst wrote:

Sorry if this has been covered before. If so I'd appreciate a heads up.

Could someone please refer me to the appropriate documentation for disabling 9.1 and enabling 9.5 on Ubuntu 16.04?

I have a production database running on 9.1 and can dump and reload without problem. I have tried the following ...

1. Dump from 9.1

2. sudo service postgresql stop (which returned without error)

3. Adjust (9.1) postgresql.conf port entry from 5432 to 5431

4. Adjust (9.5) postgresql.conf port entry to 5432

5 sudo service postgresql start (which returned without error)

When trying to createdb I'm getting the following error ...

createdb: could not connect to database template1: could not connect to server: Connection refused (0x0000274D/10061)

        Is the server running on host "pq4" (203.34.166.57)  and accepting

        TCP/IP connections on port 5432?

For the moment I have restored the conf files, restarted and we are back in production.

Any hints appreciated

Thanks

Mike