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

Mike,

Is the issue resolved ?

To a degree yes. I dumped 9.1 and loaded it into 9.5 and everything seems ok except PGAdmin4 is displaying a spurious empty undroppable database called Template0. Don't know how to get rid of that. It is invisible to PGAdmin3 running on an older machine.

If not in case, are the following covered?

And if could you provide me on the below info? 

1) Is the backup for the required database taken from the instance 9.1 and 
stored in the same server? 

I'm not using backup but rather regularly dumping the 9.1 databases from an external location and storing them off-site. I believe this is secure because the external location is in the server's ACL and access cannot be gained from anywhere else. Similarly the listen_addresses.

I realise this doesn't cover database on-disk changes.

2) If yes then stop 9.1 server and make sure only postgres 9.5 is in active 
state(you can do this as "su postgres"  user) 

Not sure what you mean? Not sure how to stop one server without stopping another. Typically I use sudo service postgres stop/start/restart and that seems to work on all together.

3) Ensure the active listening postgres ports on the server by:            netstat -alp | grep "5432" 

mike@pq4:~$ netstat -alp | grep "543"
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 localhost:5431          *:*                     LISTEN      -
tcp        0      0 pq4:5431                   *:*                     LISTEN      -
tcp        0      0 localhost:5433          *:*                     LISTEN      -
unix  2      [ ACC ]     STREAM     LISTENING     31377    -                   /var/run/postgresql/.s.PGSQL.5432
unix  2      [ ACC ]     STREAM     LISTENING     31378    -                   /var/run/postgresql/.s.PGSQL.5433
unix  2      [ ACC ]     STREAM     LISTENING     31438    -                   /var/run/postgresql/.s.PGSQL.5431
mike@pq4:~$

4) Later as same "su postgres user" 

mike@pq4:~$ su postgres
Password:
su: Authentication failure
mike@pq4:~$ su postgres user
Password:
su: Authentication failure
mike@pq4:~$ sudo -u postgres psql
could not change directory to "/home/mike": Permission denied
psql (9.5.14)
Type "help" for help.

postgres=# \password postgres
Enter new password:
Enter it again:
postgres=# \q
mike@pq4:~$ su postgres
Password:
su: Authentication failure
mike@pq4:~$

As you can see I don't know how to change the postgres user password. Did it a few times just to be sure.

provide the connection string like 
below:              /usr/lib/postgresql/9.5/bin/psql -d postgres -U miked -p 5432 

5) Now issue select version()(It should be Postgres 9.5) 

6) If Postgres 9.5 issue create database dbname 

7) Restore the taken backup into the newly created database. 

I can see I will have to use backup/restore to move beyond 9.5 or 9.6 and that all makes sense. But surely it is easier to look in the postgresql.conf file to see which port is being used by which installed version?

For example in my local network I have 9.1, 9.3 and 9.5 running. I think this is because the original Ubuntu LTS server was 12.04 and apt-get installed 9.1. Since then I have upgraded it to Ubuntu 16.04 and that installed 9.5. I think. Anyway, the ports in use here are 5432, 5433, 5434 and they are being used by 9.1, 9.3 and 9.5 respectively.

What I would like to do is uninstall 9.1 and 9.3. How would I do that?

Many thanks for staying in touch

Cheers

Mike

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-novice-f2132464.html