Re: Switching from 9.1 to 9.5 on Ubuntu 16.04

On 22/08/2018 2:52 PM, pavan95 wrote:
> 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
>
>