Thank you [Was: Switching from 9.1 to 9.5 on Ubuntu 16.04]

Fabio and Pavan

Thank you very much. You and Pavan have given me plenty to work with. I
should stumble a bit less now :)

Cheers

Mike

On 22/08/2018 5:56 PM, Fabio Pardi wrote:
> Answers in line here below
>
>
> On 22/08/18 07:51, Mike Dewhirst wrote:
>> 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.
>>
> Mike, template0 and template1 databases are legit and essential ones. The docs here can clarify it to you:
>
> https://www.postgresql.org/docs/9.5/static/manage-ag-templatedbs.html
>
>
>> 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.
> a dump of the database is to all effects considered to be a full backup. I do not know what you mean with 'doesn't cover database on-disk changes'
>
> as mentioned earlier, i would user pg_dumpall rather than pg_dump db_name
>
>>> 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.
> That's not possible. One command for one server.
>
> every server has its own data directory and config file. The same way you start one server you can stop it.
>
> Either using pg_ctl or init.d
>
>>> 4) Later as same "su postgres user"
>> mike(at)pq4:~$ su postgres
>> Password:
>> su: Authentication failure
>> mike(at)pq4:~$ su postgres user
>> Password:
>> su: Authentication failure
> now you are trying to login as 'postgres' user in your ubuntu
>
>> mike(at)pq4:~$ sudo -u postgres psql
>> could not change directory to "/home/mike": Permission denied
>> psql (9.5.14)
>> Type "help" for help.
>>
> here you used your sudo privileges to run 'psql' as postgres user
>
>> postgres=# \password postgres
>> Enter new password:
>> Enter it again:
>> postgres=# \q
> here you changed the postgres user password inside postgres. You did not change 'postgres' user's password on the Ubuntu system
>
>> mike(at)pq4:~$ su postgres
>> Password:
>> su: Authentication failure
>> mike(at)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.
>>
> If you have root privileges, use them.
>
>
>
>> 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?
>>
> to get a list of what's running
>
> apt list --installed | grep postgres
>
> ps -faxu command should give you an overview of what is running, you can cross the PID number with the output of 'lsof -ni :5431' to know the version running on port 5431, by instance.
>
>
> regards,
>
> fabio pardi
>
>