Thank you [Was: Switching from 9.1 to 9.5 on Ubuntu 16.04] - Mailing list pgsql-novice

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@pq4:~$ su postgres
Password:
su: Authentication failure
mike@pq4:~$ su postgres user
Password:
su: Authentication failure

now you are trying to login as 'postgres' user in your ubuntu

mike@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@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.

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