Switching from 9.1 to 9.5 on Ubuntu 16.04

[Mike Dewhirst]

Sorry if this has been covered before. If so I'd appreciate a heads up.

Could someone please refer me to the appropriate documentation for
disabling 9.1 and enabling 9.5 on Ubuntu 16.04?

I have a production database running on 9.1 and can dump and reload
without problem. I have tried the following ...

1. Dump from 9.1

2. sudo service postgresql stop (which returned without error)

3. Adjust (9.1) postgresql.conf port entry from 5432 to 5431

4. Adjust (9.5) postgresql.conf port entry to 5432

5 sudo service postgresql start (which returned without error)

When trying to createdb I'm getting the following error ...

createdb: could not connect to database template1: could not connect to
server: Connection refused (0x0000274D/10061)
        Is the server running on host "pq4" (203.34.166.57) and accepting
        TCP/IP connections on port 5432?

For the moment I have restored the conf files, restarted and we are back
in production.

Any hints appreciated

Thanks

Mike

[Fabio Pardi]

Hi Mike,

best (and must) for you and your customers would be if you could test on a non production env. (and obscure IP addresses from your messages)


In order to do not lose data I would do the following steps instead:

1. Block access to database so that nobody except you can read or write.

2. dump db 9.1

3. stop db 9.1

4 start db 9.5

5. import dump to 9.5

6. restore access


Note that you might need to change your postgresql.conf file because of the introduction of 'max_wal_size' from 9.5 on.

Also note that in your proposed procedure here below, you might lose new data inserted between 1 and 2 because the dump is a snapshot taken at the time you initiate the process: new data that comes in after that, is not included in it.

Also something to keep in mind is the size of the dump. if is very big, you might want to go through the pg_ugrade procedure in order to keep downtime window smaller.

regards,

fabio pardi

Post by Mike Dewhirst
Sorry if this has been covered before. If so I'd appreciate a heads up.
Could someone please refer me to the appropriate documentation for disabling 9.1 and enabling 9.5 on Ubuntu 16.04?
I have a production database running on 9.1 and can dump and reload without problem. I have tried the following ...
1. Dump from 9.1
2. sudo service postgresql stop (which returned without error)
3. Adjust (9.1) postgresql.conf port entry from 5432 to 5431
4. Adjust (9.5) postgresql.conf port entry to 5432
5 sudo service postgresql start (which returned without error)
When trying to createdb I'm getting the following error ...
createdb: could not connect to database template1: could not connect to server: Connection refused (0x0000274D/10061)
        Is the server running on host "pq4" (203.34.166.57) and accepting
        TCP/IP connections on port 5432?
For the moment I have restored the conf files, restarted and we are back in production.
Any hints appreciated
Thanks
Mike

[Fabio Pardi]

Hi Mike,


please keep the mailing list posted, so maybe other people can help you too or get help from your problem.


if your config files have errors, your server will refuse to start. You can read in the logfile what is happeningand report it to us in case should you need help.

The fact that when you start postgresql it does not return errors, is suspicious to me, therefore it might be that everything is ok, but you cannot connect to your instance.

Maybe answering to the following, will give to us a better picture:

- do you see postgres running on the processes listof the system after you start 9.5 instance?

- do you have special needs to run 'createdb'? because a pg_dumpall might turn out handy, depending from your situation (instead of pg_dump)

- can you connect to your db instance running 'psql' before and after the operations? (as in: before stopping 9.1 - after starting 9.1 - after starting 9.5 )

- I think your previous point '/3. Adjust (9.1) postgresql.conf port entry from 5432 to 5431' is not needed in your case, since after the dump you do not need 9.1 any longer. Or there is anything I am missing?/

/
/

hope it helps,

fabio pardi

Fabio
Yes. I should have said I have been testing on a duplicate VM copy of the system. As soon as I can do it successfully I'll schedule the upgrade and announce downtime for the process.
My problem is that I cannot switch the 9.1 server off and the 9.5 server on!
I'm doing something wrong!
M
/Connected by Motorola/
/
/
/
/
/
/
/Hi Mike, /
/
/
/best (and must) for you and your customers would be if you could test on a non production env. (and obscure IP addresses from your messages)/
/
/
/
/
/In order to do not lose data I would do the following steps instead:/
/
/
/1. Block access to database so that nobody except you can read or write. /
/
/
/2. dump db 9.1/
/
/
/3. stop db 9.1/
/
/
/4 start db 9.5/
/
/
/5. import dump to 9.5/
/
/
/6. restore access/
/
/
/
/
/Note that you might need to change your postgresql.conf file because of the introduction of 'max_wal_size' from 9.5 on./
/
/
/Also note that in your proposed procedure here below, you might lose new data inserted between 1 and 2 because the dump is a snapshot taken at the time you initiate the process: new data that comes in after that, is not included in it./
/
/
/Also something to keep in mind is the size of the dump. if is very big, you might want to go through the pg_ugrade procedure in order to keep downtime window smaller./
/
/
/regards,/
/
/
/fabio pardi/
/
/
/
/
/
/
/On 20/08/18 13:21, Mike Dewhirst wrote:/
/
/
/Sorry if this has been covered before. If so I'd appreciate a heads up./
/
/
/Could someone please refer me to the appropriate documentation for disabling 9.1 and enabling 9.5 on Ubuntu 16.04?/
/
/
/I have a production database running on 9.1 and can dump and reload without problem. I have tried the following .../
/
/
/1. Dump from 9.1 /
/
/
/2. sudo service postgresql stop (which returned without error)/
/
/
/3. Adjust (9.1) postgresql.conf port entry from 5432 to 5431/
/
/
/4. Adjust (9.5) postgresql.conf port entry to 5432/
/
/
/5 sudo service postgresql start (which returned without error)/
/
/
/When trying to createdb I'm getting the following error .../
/
/
/createdb: could not connect to database template1: could not connect to server: Connection refused (0x0000274D/10061)/
/        Is the server running on host "pq4" (//203.34.166.57/ <http://203.34.166.57>/)  and accepting/
/        TCP/IP connections on port 5432?/
/
/
/For the moment I have restored the conf files, restarted and we are back in production./
/
/
/Any hints appreciated /
/
/
/Thanks/
/
/
/Mike/
/
/

[Mike Dewhirst]

Post by Fabio Pardi
Hi Mike,
please keep the mailing list posted, so maybe other people can help
you too or get help from your problem.

That was finger trouble - sorry.

Post by Fabio Pardi
if your config files have errors, your server will refuse to start.
You can read in the logfile what is happeningand report it to us in
case should you need help.

I tried to get everything working in a dry run but failed with
frustrating mistakes. I decided to get the new server running on port
5435 and was somewhat successful but no cigar. I needed the ISP to open
that port so I could manage the change externally from PGAdmin but it
was going to take him all day.

In the end I couldn't stand it any more and announced an unscheduled
maintenance with an opportunity for the users to veto. I left them with
readonly access and voluntary abstaining from writing. Otherwise I would
have had to take down two websites for the duration.

It is all working now - but with wrinkles. This is what I did ...

Dumped 2 databases

Swapped the port numbers so 9.1 was on port 5431 and 9.5 was on 5432

Restarted Postgres

I couldn't get PGAdmin4 to cooperate so I reverted to an old laptop with
PGAdmin3 and discovered I needed to set the postgres password which I
did via SSH on the production server using psql \password postgres

Tried creating my own miked user in psql CREATE ROLE 'miked' WITH
SUPERUSER PASSWORD 'whatever' but that failed

Back in PGAdmin3 successfully created role miked as superuser

Back on the old laptop moved the dump files to within reach and loaded
them both up on the 9.5 server using psql - successfully.

Did some sanity checks on the data (checked some session records etc)
and proved everything is working in both databases and websites are
working ok. Handed it all back to the users.

Swapped laptops and tried PGAdmin4 but got a "procpid" error connecting
which went away after reloading the configuration and refreshing the page

The wrinkle is that PGAdmin4 is showing an extra database which I tried
to drop like so ...



2018-08-21 16:12:23 AEST [5247-1] ***@postgres ERROR: cannot drop a template database
2018-08-21 16:12:23 AEST [5247-2] ***@postgres STATEMENT: DROP DATABASE template0;
2018-08-21 16:12:23 AEST [5271-1] ***@template0 FATAL: database "template0" is not currently accepting connections
2018-08-21 16:50:02 AEST [5658-1] ***@1650 FATAL: database "1650" does not exist


I have no idea what database 1650 might be.

postgres  |  postgres |  default administrative connection database

ssds   |  miked    |

template0 |  postgres |  unmodifiable empty database

train   |  miked    |


This does not show in PGAdmin3 on the other laptop

All in all I'm relieved. I could always have reverted to 9.1 and I'm
glad I don't have to.

I just need to delete 9.1 now and maybe learn to live with template0
database showing in PGAdmin4

Thanks Fabio

Mike

Post by Fabio Pardi
The fact that when you start postgresql it does not return errors, is
suspicious to me, therefore it might be that everything is ok, but you
cannot connect to your instance.
- do you see postgres running on the processes listof the system after
you start 9.5 instance?
- do you have special needs to run 'createdb'? because a pg_dumpall
might turn out handy, depending from your situation (instead of pg_dump)
- can you connect to your db instance running 'psql' before and after
the operations? (as in: before stopping 9.1 - after starting 9.1 -
after starting 9.5 )
- I think your previous point '/3. Adjust (9.1) postgresql.conf port
entry from 5432 to 5431' is not needed in your case, since after the
dump you do not need 9.1 any longer. Or there is anything I am missing?/
/
/
hope it helps,
fabio pardi

Fabio
Yes. I should have said I have been testing on a duplicate VM copy of
the system. As soon as I can do it successfully I'll schedule the
upgrade and announce downtime for the process.
My problem is that I cannot switch the 9.1 server off and the 9.5 server on!
I'm doing something wrong!
M
/Connected by Motorola/
/
/
/
/
/
/
/Hi Mike, /
/
/
/best (and must) for you and your customers would be if you could
test on a non production env. (and obscure IP addresses from your
messages)/
/
/
/
/
/In order to do not lose data I would do the following steps instead:/
/
/
/1. Block access to database so that nobody except you can read or write. /
/
/
/2. dump db 9.1/
/
/
/3. stop db 9.1/
/
/
/4 start db 9.5/
/
/
/5. import dump to 9.5/
/
/
/6. restore access/
/
/
/
/
/Note that you might need to change your postgresql.conf file because
of the introduction of 'max_wal_size' from 9.5 on./
/
/
/Also note that in your proposed procedure here below, you might lose
new data inserted between 1 and 2 because the dump is a snapshot
taken at the time you initiate the process: new data that comes in
after that, is not included in it./
/
/
/Also something to keep in mind is the size of the dump. if is very
big, you might want to go through the pg_ugrade procedure in order to
keep downtime window smaller./
/
/
/regards,/
/
/
/fabio pardi/
/
/
/
/
/
/
/On 20/08/18 13:21, Mike Dewhirst wrote:/
/
/
/Sorry if this has been covered before. If so I'd appreciate a heads up./
/
/
/Could someone please refer me to the appropriate documentation for
disabling 9.1 and enabling 9.5 on Ubuntu 16.04?/
/
/
/I have a production database running on 9.1 and can dump and reload
without problem. I have tried the following .../
/
/
/1. Dump from 9.1 /
/
/
/2. sudo service postgresql stop (which returned without error)/
/
/
/3. Adjust (9.1) postgresql.conf port entry from 5432 to 5431/
/
/
/4. Adjust (9.5) postgresql.conf port entry to 5432/
/
/
/5 sudo service postgresql start (which returned without error)/
/
/
/When trying to createdb I'm getting the following error .../
/
/
/createdb: could not connect to database template1: could not connect
to server: Connection refused (0x0000274D/10061)/
/        Is the server running on host "pq4" (//203.34.166.57/
<http://203.34.166.57>/)  and accepting/
/        TCP/IP connections on port 5432?/
/
/
/For the moment I have restored the conf files, restarted and we are back in production./
/
/
/Any hints appreciated /
/
/
/Thanks/
/
/
/Mike/
/
/

[pavan95]

Hi Mike,

Are both Postgresql instances 9.5 and 9.1 are on same server??

Post by Mike Dewhirst
When trying to createdb I'm getting the following error ...

What is the exact statement you have specified did you explicitly specified
any port??

My quick questions:

1) What does "sudo service postgresql status" give as root user?

2) Login as /su postgres/ and try finding the status of both 9.1 and 9.5
individually as shown below and paste the output:

/usr/lib/postgresql/9.5/bin/pg_ctl -D "/var/lib/postgresql/9.5/main" status
/usr/lib/postgresql/9.1/bin/pg_ctl -D "/var/lib/postgresql/9.1/main" status


It will help us to know which port is active postgres referring to?


Regards,
Pavan.



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

[Mike Dewhirst]

Post by Fabio Pardi
Hi Mike,
Are both Postgresql instances 9.5 and 9.1 are on same server??

Post by Mike Dewhirst
When trying to createdb I'm getting the following error ...

What is the exact statement you have specified did you explicitly specified
any port??

createdb --port=5432 --host=pq4 --template=template0 --encoding=UTF8
--lc-collate=C --lc-ctype=C --username=miked --owner=miked train

(pq4 is in my hosts file)

Post by Fabio Pardi
1) What does "sudo service postgresql status" give as root user?

***@pq4:~$ sudo service postgresql status
[sudo] password for mike:
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled;
vendor preset: enabled)
   Active: active (exited) since Tue 2018-08-21 15:53:16 AEST; 4h 57min ago
  Process: 5041 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 5041 (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/postgresql.service

Aug 21 15:53:16 pq4 systemd[1]: Starting PostgreSQL RDBMS...
Aug 21 15:53:16 pq4 systemd[1]: Started PostgreSQL RDBMS.

Post by Fabio Pardi
2) Login as /su postgres/ and try finding the status of both 9.1 and 9.5
/usr/lib/postgresql/9.5/bin/pg_ctl -D "/var/lib/postgresql/9.5/main" status

***@pq4:~$ sudo -u postgres /usr/lib/postgresql/9.5/bin/pg_ctl -D
"/var/lib/postgresql/9.5/main" status
could not change directory to "/home/mike": Permission denied
pg_ctl: server is running (PID: 4992)
/usr/lib/postgresql/9.5/bin/postgres "-D" "/var/lib/postgresql/9.5/main"
"-c" "config_file=/etc/postgresql/9.5/main/postgresql.conf"

Post by Fabio Pardi
/usr/lib/postgresql/9.1/bin/pg_ctl -D "/var/lib/postgresql/9.1/main" status

***@pq4:~$
***@pq4:~$ sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_ctl -D
"/var/lib/postgresql/9.1/main" status
could not change directory to "/home/mike"
pg_ctl: server is running (PID: 4993)
/usr/lib/postgresql/9.1/bin/postgres "-D" "/var/lib/postgresql/9.1/main"
"-c" "config_file=/etc/postgresql/9.1/main/postgresql.conf"

Post by Fabio Pardi
It will help us to know which port is active postgres referring to?

Pavan

You may not have seen my recent response to Fabio's advice but the
switch has been more or less successful except for a couple of bits and
pieces I don't understand.

Not sure how to deal with them but I have just lost *all* keyboard
privileges for the evening :(

Cheers

Mike

Post by Fabio Pardi
Regards,
Pavan.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-novice-f2132464.html

[pavan95]

Mike,

Post by Mike Dewhirst
createdb --port=5432 --host=pq4 --template=template0 --encoding=UTF8

--lc-collate=C --lc-ctype=C --username=miked --owner=miked train

Is the dbname specified
"/var/lib/postgresql/9.1/main" status
could not change directory to "/home/mike"

Post by Mike Dewhirst
pg_ctl: server is running (PID: 4993)
/usr/lib/postgresql/9.1/bin/postgres "-D" "/var/lib/postgresql/9.1/main"

"-c" "config_file=/etc/postgresql/9.1/main/postgresql.conf"

Why is Postgres 9.1 is in running state? Your requirement is to disable 9.1
& enable 9.5 right??

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?

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)

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

4) Later as same "su postgres user" 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 think this should suffice. Any misinterpretations on my side could be
corrected.


Regards,
Pavan



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

[Mike Dewhirst]

Post by Mike Dewhirst
Mike,

Post by Mike Dewhirst
createdb --port=5432 --host=pq4 --template=template0 --encoding=UTF8

--lc-collate=C --lc-ctype=C --username=miked --owner=miked train
Is the dbname specified

Yes - train is the dbname

Post by Mike Dewhirst
"/var/lib/postgresql/9.1/main" status
could not change directory to "/home/mike"

Post by Mike Dewhirst
pg_ctl: server is running (PID: 4993)
/usr/lib/postgresql/9.1/bin/postgres "-D" "/var/lib/postgresql/9.1/main"

"-c" "config_file=/etc/postgresql/9.1/main/postgresql.conf"
Why is Postgres 9.1 is in running state? Your requirement is to disable 9.1
& enable 9.5 right??
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?
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)
netstat -alp | grep "5432"
4) Later as same "su postgres user" provide the connection string like
/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 think this should suffice. Any misinterpretations on my side could be
corrected.
Regards,
Pavan
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-novice-f2132464.html

[pavan95]

Mike,

Is the issue resolved ?

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?

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)

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

4) Later as same "su postgres user" 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.



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

[Mike Dewhirst]

Post by pavan95
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.

Post by pavan95
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.

Post by pavan95
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.

Post by pavan95
netstat -alp | grep "5432"

***@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

Post by pavan95
4) Later as same "su postgres user"

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

Post by pavan95
provide the connection string like
/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

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

[Fabio Pardi]

Answers in line here below

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

Post by pavan95
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

Post by pavan95
4) Later as same "su postgres user"

su: Authentication failure
su: Authentication failure

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

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
postgres=# \q

here you changed the postgres user password inside postgres. You did not change 'postgres' user's password on the Ubuntu system

su: Authentication failure
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

[Mike Dewhirst]

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

Post by Fabio Pardi
Answers in line here below

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.

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

Post by pavan95
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

Post by pavan95
4) Later as same "su postgres user"

su: Authentication failure
su: Authentication failure

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

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
postgres=# \q

here you changed the postgres user password inside postgres. You did not change 'postgres' user's password on the Ubuntu system

su: Authentication failure
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

[pavan95]

Mike,

Post by Mike Dewhirst

Post by pavan95
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)

Post by Mike Dewhirst
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.

See, in /etc/init.d/ folder postgres is created as service so action on that
service will be equivalently effected for all the postgres instances on this
server. For example you do have postgres 9.1,9.3,9.5 on the same ubuntu
server and if you issue:
service postgresql start/stop/restart
it will actually affect all the 3 instances. So you need to be specific and
issue command(as previously said) like :
/usr/lib/postgresql/9.5/bin/pg_ctl -D "/var/lib/postgresql/9.5/main" start
/usr/lib/postgresql/9.5/bin/pg_ctl -D "/var/lib/postgresql/9.5/main" stop
etc...

Note: for issuing above commands you should be postgres user(from OS level
and DB level)

Post by Mike Dewhirst

Post by pavan95

Post by Mike Dewhirst
createdb --port=5432 --host=pq4 --template=template0 --encoding=UTF8

--lc-collate=C --lc-ctype=C --username=miked --owner=miked train

Post by Mike Dewhirst

Post by pavan95
netstat -alp | grep "5432"

(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

-

Post by Mike Dewhirst
tcp 0 0 pq4:5431 *:*

LISTEN -

Post by Mike Dewhirst
tcp 0 0 localhost:5433 *:* LISTEN

-

Post by Mike Dewhirst
unix 2 [ ACC ] STREAM LISTENING 31377 -

/var/run/postgresql/.s.PGSQL.5432

Post by Mike Dewhirst
unix 2 [ ACC ] STREAM LISTENING 31378 -

/var/run/postgresql/.s.PGSQL.5433

Post by Mike Dewhirst
unix 2 [ ACC ] STREAM LISTENING 31438 -

/var/run/postgresql/.s.PGSQL.5431
From the above output you are trying to create a database on database port
5432 which is not active. Postgres is unable to understand how to create a
database on a closed port(which is not going to work for sure). Ensure
yourself that the port on which you are creating database is in open state
and accepting client requests.

Post by Mike Dewhirst

Post by pavan95
4) Later as same "su postgres user"

su: Authentication failure
su: Authentication failure
could not change directory to "/home/mike": Permission denied
psql (9.5.14)
Type "help" for help.
postgres=# \password postgres
postgres=# \q
su: Authentication failure
As you can see I don't know how to change the postgres user password. Did

it a few times just to be sure.

Firstly, do you have root access for that server?

If yes, you can issue sudo bash as the user(you will login to the server)
and then you can change the password for the OS level postgres user like
below:

[***@pg4]# passwd postgres
Changing password for user postgres.
New password:
Confirm New password:

then password will be changed for that user and can try logging in as :
[***@pg4]# su postgres
[***@pg4]#

Now issue the above said commands

/usr/lib/postgresql/9.5/bin/pg_ctl -D "/var/lib/postgresql/9.5/main" start
/usr/lib/postgresql/9.5/bin/pg_ctl -D "/var/lib/postgresql/9.5/main" stop
etc..


Which will work for sure...

Post by Mike Dewhirst
What I would like to do is uninstall 9.1 and 9.3. How would I do that?

issue the below command:
*dpkg -l | grep postgresql *

Consider removing the postgres versions you like to remove!! Anyways this is
not a proper fix for your prob.

Jus ensure the port is listening on your server to execute your createdb
command. Acknowledge for further queries if any.

Regards,
Pavan,
9841380956







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

[Mike Dewhirst]

Pavan and Fabio

I always felt Postgres was a good choice for me and your support in this
list has really confirmed it.

Thank you very much

Cheers

Mike

Post by pavan95
Mike,

Post by Mike Dewhirst

Post by pavan95
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)

Post by Mike Dewhirst
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.
See, in /etc/init.d/ folder postgres is created as service so action on that
service will be equivalently effected for all the postgres instances on this
server. For example you do have postgres 9.1,9.3,9.5 on the same ubuntu
service postgresql start/stop/restart
it will actually affect all the 3 instances. So you need to be specific and
/usr/lib/postgresql/9.5/bin/pg_ctl -D "/var/lib/postgresql/9.5/main" start
/usr/lib/postgresql/9.5/bin/pg_ctl -D "/var/lib/postgresql/9.5/main" stop
etc...
Note: for issuing above commands you should be postgres user(from OS level
and DB level)

Post by Mike Dewhirst

Post by pavan95

Post by Mike Dewhirst
createdb --port=5432 --host=pq4 --template=template0 --encoding=UTF8

--lc-collate=C --lc-ctype=C --username=miked --owner=miked train

Post by Mike Dewhirst

Post by pavan95
netstat -alp | grep "5432"

(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

-

Post by Mike Dewhirst
tcp 0 0 pq4:5431 *:*

LISTEN -

Post by Mike Dewhirst
tcp 0 0 localhost:5433 *:* LISTEN

-

Post by Mike Dewhirst
unix 2 [ ACC ] STREAM LISTENING 31377 -

/var/run/postgresql/.s.PGSQL.5432

Post by Mike Dewhirst
unix 2 [ ACC ] STREAM LISTENING 31378 -

/var/run/postgresql/.s.PGSQL.5433

Post by Mike Dewhirst
unix 2 [ ACC ] STREAM LISTENING 31438 -

/var/run/postgresql/.s.PGSQL.5431

Post by Mike Dewhirst
From the above output you are trying to create a database on database port

5432 which is not active. Postgres is unable to understand how to create a
database on a closed port(which is not going to work for sure). Ensure
yourself that the port on which you are creating database is in open state
and accepting client requests.

Post by Mike Dewhirst

Post by pavan95
4) Later as same "su postgres user"

su: Authentication failure
su: Authentication failure
could not change directory to "/home/mike": Permission denied
psql (9.5.14)
Type "help" for help.
postgres=# \password postgres
postgres=# \q
su: Authentication failure
As you can see I don't know how to change the postgres user password. Did

it a few times just to be sure.
Firstly, do you have root access for that server?
If yes, you can issue sudo bash as the user(you will login to the server)
and then you can change the password for the OS level postgres user like
Changing password for user postgres.
Now issue the above said commands
/usr/lib/postgresql/9.5/bin/pg_ctl -D "/var/lib/postgresql/9.5/main" start
/usr/lib/postgresql/9.5/bin/pg_ctl -D "/var/lib/postgresql/9.5/main" stop
etc..
Which will work for sure...

Post by Mike Dewhirst
What I would like to do is uninstall 9.1 and 9.3. How would I do that?

*dpkg -l | grep postgresql *
Consider removing the postgres versions you like to remove!! Anyways this is
not a proper fix for your prob.
Jus ensure the port is listening on your server to execute your createdb
command. Acknowledge for further queries if any.
Regards,
Pavan,
9841380956
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-novice-f2132464.html