Обсуждение: Switching from 9.1 to 9.5 on Ubuntu 16.04

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

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

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

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 happening and 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 list of 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

Fabio Pardi <f.pardi@portavita.eu> wrote:

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)  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

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.

if your config files have errors, your server will refuse to start. You can read in the logfile what is happening and 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] miked@postgres ERROR:  cannot drop a template database
2018-08-21 16:12:23 AEST [5247-2] miked@postgres STATEMENT:  DROP DATABASE template0;
2018-08-21 16:12:23 AEST [5271-1] miked@template0 FATAL:  database "template0" is not currently accepting connections
2018-08-21 16:50:02 AEST [5658-1] miked@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

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 list of 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

On 20/08/18 15:43, Mike Dewhirst wrote:

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

Fabio Pardi <f.pardi@portavita.eu> wrote:

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)  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

Hi Mike,

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

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

Hi Mike,

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

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)

My quick questions:

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

mike@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.
mike@pq4:~$

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

mike@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"
mike@pq4:~$

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

mike@pq4:~$
mike@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"
mike@pq4:~$

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

Regards,
Pavan.



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

Mike,

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

Is the dbname specified

>mike@pq4:~$
>mike@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"

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,

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

mike@pq4:~$
mike@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"

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,

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,

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

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

Mike,

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

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)

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

>>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:~$

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.

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

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:

[root@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 :
[root@pg4]# su postgres
[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...

>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

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

Mike,

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.

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)

createdb --port=5432 --host=pq4 --template=template0 --encoding=UTF8 

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

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:~$

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

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.

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:

[root@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 :
[root@pg4]# su postgres
[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...

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