📋Steps in this guide1/9

How to change port number in postgres - DBACLASS DBACLASS

This article explains how to change the port number in postgres. You need to update the postgresql.conf file and restart the service.

postgresql configurationintermediate

by PostgreSQL

14 views

1. Check the existing port details

Code/Command (click line numbers to comment):

postgres=# <span style="color: #ff6600;"><strong>select * from pg_settings where name='port';</strong></span>
-[ RECORD 1 ]---+-----------------------------------------------------
name            | port
setting         | 5444
unit            |
category        | Connections and Authentication / Connection Settings
short_desc      | Sets the TCP port the server listens on.
extra_desc      |
context         | postmaster
vartype         | integer
source          | configuration file
min_val         | 1
max_val         | 65535
enumvals        |
boot_val        | 5444
reset_val       | 5444
sourcefile      | /pgdata/data/postgresql.conf
sourceline      | 63
pending_restart | f


postgres=# <span style="color: #ff6600;"><strong>\conninfo</strong></span>
You are connected to database "postgres" as user "enterprisedb" via socket in "/tmp" at port "5444".
postgres=#<span style="color: #ff6600;"><strong> show config_file;</strong></span>
           config_file
---------------------------------
 /pgdata/data/postgresql.conf
(1 row)

[enterprisedb@master ~]$ <span style="color: #ff6600;"><strong> cat /pgdata/data/postgresql.conf | grep 'port'</strong></span>
port = 5444                             # (change requires restart)

2. Update the port in postgresql.conf file:

Code/Command (click line numbers to comment):

-- change the port from 5444 to 5432

[enterprisedb@master ~]$  <span style="color: #ff6600;"><strong>cat /pgdata/data/postgresql.conf | grep 'port'</strong></span>
port = 5432

3. restart postgres services:

Alternatively you can restart the service, if configured.

Code/Command (click line numbers to comment):

<span style="color: #ff6600;">pg_ctl stop -D /pgdata/data
pg_ctl start -D /pgdata/data
</span>

root# <span style="color: #ff6600;"><strong>systemctl stop edb-as-11</strong></span>
root#<span style="color: #ff6600;"><strong> systemctl start edb-as-11
</strong></span>

4. Check whether port has been updated

We can see , the port has been updated to 5432. Now If any streaming replication is enabled, then we need to update the primary server port in recovery.conf file of standby server.

Code/Command (click line numbers to comment):

[enterprisedb@master ~]$psql -d postgres -p 5432

postgres=# <span style="color: #ff6600;"><strong>\x</strong></span>
Expanded display is on.

postgres=# <span style="color: #ff6600;"><strong>select * from pg_settings where name='port';</strong></span>
-[ RECORD 1 ]---+-----------------------------------------------------
name            | port
setting         | 5432
unit            |
category        | Connections and Authentication / Connection Settings
short_desc      | Sets the TCP port the server listens on.
extra_desc      |
context         | postmaster
vartype         | integer
source          | configuration file
min_val         | 1
max_val         | 65535
enumvals        |
boot_val        | 5444
reset_val       | 5432
sourcefile      | /pgdata/data/postgresql.conf
sourceline      | 63
pending_restart | f

5. Check for any streaming replication ( run On primary server)

It shows replication is enabled to server 10.20.30.77(standby server). So we need to update the recovery.conf file in that standby server.

Code/Command (click line numbers to comment):

postgres=# <span style="color: #ff6600;"><strong>select * from pg_stat_replication;</strong></span>
-[ RECORD 1 ]----+---------------------------------
pid              | 2800
usesysid         | 10
usename          | enterprisedb
application_name | walreceiver
client_addr      | 10.20.30.77
client_hostname  |
client_port      | 45884
backend_start    | 01-JUN-21 09:38:07.003029 +03:00
backend_xmin     |
state            | streaming
sent_lsn         | 0/F001AB8
write_lsn        | 0/F001AB8
flush_lsn        | 0/F001AB8
replay_lsn       | 0/F001AB8
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async

6.Update the recovery.conf file in standby server.

Code/Command (click line numbers to comment):

-- recovery.conf file resides inside data directory.
[enterprisedb@standby]$ <span style="color: #ff6600;"><strong>cat /pgdata/data/recovery.conf</strong></span>
standby_mode = 'on'
primary_conninfo = 'user=enterprisedb password=edbpostgres#123 host=10.20.30.76 <span style="color: #ff6600;"><strong>port=5432</strong></span> sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any'
primary_slot_name = 'slot1'

7. Updating the postgresql.conf file:

Just like primary, if you want to change the listening port from 5444 to 5432 in standby( just like primary) also, then update the postgresql.conf file in standby server also. Otherwise you can continue with the same port.

Code/Command (click line numbers to comment):

1
2

[enterprisedb@master ~]$ <span style="color: #ff6600;"><strong> cat /pgdata/data/postgresql.conf | grep 'port'</strong></span>
port = 5432                             # (change requires restart)

7.Restart the pg services in standby server.

Alternatively you can restart the service, if configured.

Code/Command (click line numbers to comment):

pg_ctl stop -D /pgdata/data
pg_ctl start -D /pgdata/data

root# <span style="color: #ff6600;"><strong>systemctl stop edb-as-11</strong></span>
root# <span style="color: #ff6600;"><strong>systemctl start edb-as-11
</strong></span>

8.Check replication status on standby:

Code/Command (click line numbers to comment):

postgres=#<span style="color: #ff6600;"><strong> select * from pg_stat_wal_receiver;</strong></span>
-[ RECORD 1 ]---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 8124
status                | streaming
receive_start_lsn     | 0/D000000
receive_start_tli     | 1
received_lsn          | 0/F001AB8
received_tli          | 1
last_msg_send_time    | 01-JUN-21 16:59:57.746814 +03:00
last_msg_receipt_time | 01-JUN-21 16:59:57.747272 +03:00
latest_end_lsn        | 0/F001AB8
latest_end_time       | 01-JUN-21 09:54:24.322036 +03:00
slot_name             | slot1
sender_host           | 10.20.30.76
sender_port           | 5432
conninfo              | user=enterprisedb password=******** dbname=replication host=10.20.30.76 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any

Comments (0)

Please to add comments

No comments yet. Be the first to comment!