Re: 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
emptyundroppable database called Template0. Don't know how to get rid of that. It is invisible to PGAdmin3 running on
anolder 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
anywhereelse. 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
coverdatabase 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
postgresstop/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
Ubuntusystem
 

> 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
easierto 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
serverwas 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