This article explains how to change the port number in postgres. We will change the port from 5444 to 5432 .
1. Check the existing port details
postgres=# select * from pg_settings where name='port'; -[ 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=# \conninfo You are connected to database "postgres" as user "enterprisedb" via socket in "/tmp" at port "5444".postgres=# show config_file; config_file --------------------------------- /pgdata/data/postgresql.conf (1 row) [enterprisedb@master ~]$ cat /pgdata/data/postgresql.conf | grep 'port' port = 5444 # (change requires restart)
2. Update the port in postgresql.conf file:
-- change the port from 5444 to 5432
[enterprisedb@master ~]$ cat /pgdata/data/postgresql.conf | grep 'port'
port = 5432
3. restart postgres services:
pg_ctl stop -D /pgdata/data
pg_ctl start -D /pgdata/data
Alternatively you can restart the service, if configured.
root# systemctl stop edb-as-11
root# systemctl start edb-as-11
4. Check whether port has been updated
[enterprisedb@master ~]$psql -d postgres -p 5432
postgres=# \x
Expanded display is on.
postgres=# select * from pg_settings where name='port';
-[ 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
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.
5. Check for any streaming replication ( run On primary server)
postgres=# select * from pg_stat_replication;
-[ 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
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.
6.Update the recovery.conf file in standby server.
-- recovery.conf file resides inside data directory.
[enterprisedb@standby]$ cat /pgdata/data/recovery.conf
standby_mode = 'on'
primary_conninfo = 'user=enterprisedb password=edbpostgres#123 host=10.20.30.76 port=5432 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.
[enterprisedb@master ~]$ cat /pgdata/data/postgresql.conf | grep 'port'
port = 5432 # (change requires restart)
7.Restart the pg services in standby server.
pg_ctl stop -D /pgdata/data
pg_ctl start -D /pgdata/data
Alternatively you can restart the service, if configured.
root# systemctl stop edb-as-11
root# systemctl start edb-as-11
8.Check replication status on standby:
postgres=# select * from pg_stat_wal_receiver;
-[ 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
