Как изменить порт postgresql windows

Ports are used to establish connections remotely between multiple nodes in a network to transfer data or perform some computational operations. PostgreSQL is generally running on the port number “5432” and the user can simply connect to the PostgreSQL server using this port. The platform allows the user to change its default port if somehow “5432” is not responding or is occupied elsewhere.

This guide will explain how to change the default port in PostgreSQL.

How to Change Default Port in PostgreSQL?

To change the port of the PostgreSQL, connect to the PostgreSQL database using the following query:

psql -U postgres

Running the above query will prompt the user to enter the master password for the Postgres user:

Картинка с сайта: www.commandprompt.com

Use the following command to get all the data from the pg_settings view and use the WHERE clause to get the data about the port:

select * from pg_settings where name='port';

Running the above query displays the default port which is “5432” of the PostgreSQL database:

Картинка с сайта: www.commandprompt.com

Use the following command to get the port for the database and the user to which you are connected:

\conninfo

Type the following command to get the location of the file where the default port is stored:

show config_file;

The above command displays the path of the file where the port is saved:

Картинка с сайта: www.commandprompt.com

Quit the PostgreSQL and head into the data directory of the PostgreSQL from the local system:

cd C:\Program` Files\PostgreSQL\15\data

Find the port number from the “postgresql.conf” file:

cat postgresql.conf | findstr 'port'

The port on each of the files on which the PostgreSQL database is running is “5432” so it is the default port for the PostgreSQL:

Картинка с сайта: www.commandprompt.com

Locate the “postgresql.conf” file from the local system and open it:

Картинка с сайта: www.commandprompt.com

Locate the port number from the file and change it from “5432” to “6543” before saving it:

Картинка с сайта: www.commandprompt.com

After saving the file, use the following command again to get the updated port number:

cat postgresql.conf | findstr 'port'

The above command displays the “6543” port number:

Картинка с сайта: www.commandprompt.com

Open the “Run” application from the local system:

Картинка с сайта: www.commandprompt.com

Type the “services.msc” and click on the “OK” button:

Картинка с сайта: www.commandprompt.com

Locate the PostgreSQL file, select it, and click on the “Restart” button:

Картинка с сайта: www.commandprompt.com

Once the service is restarted head inside PostgreSQL and type the following command:

select * from pg_settings where name='port';

The default port has been changed successfully as displayed in the screenshot below:

Картинка с сайта: www.commandprompt.com

That’s all about changing the default port in the PostgreSQL database.

Conclusion

To change the default port in PostgreSQL, simply connect to the PostgreSQL server by providing the master password for the user. After that, get the specific data about the port from the pg_settings view to get the default port number on which it is running. Use the “show config_file” command to get the path of the file and then change the port number from that file. Restart the service from the “service.msc” application and check the updated default port number.

PostgreSQL is one of the most powerful and widely used relational database management systems (RDBMS) in the world. By default, PostgreSQL listens for incoming connections on port 5432.

In this article, we will describe the process of changing the default port for PostgreSQL in detailed and step-by-step manner from basic configurations to advanced setups.

1. Where is the Default Port Defined?

The default port for PostgreSQL is specified in the Postgresql.conf configuration file. This file controls various settings for the PostgreSQL server, including the port it listens on.

• By default, the port is set to 5432, but we can change it to any other port number (within the valid range of 1024 to 65535) according to our needs.

2. Why Change the Default Port?

There are several reasons we might want to change the default port (5432) for the PostgreSQL instance:

Security

Using a non-default port can add an additional layer of «security through obscurity». While this does not replace proper security practices, it can help reduce exposure to automated attacks or unwanted traffic.

Running Multiple PostgreSQL Instances

If we want to run multiple instances of PostgreSQL on the same server (for example, development, staging, and production databases), we will need to assign different ports to each instance.

Port Conflicts

Sometimes, other services may already use port 5432 (such as another PostgreSQL instance, or a different database service). In such cases, changing the port is a simple way to avoid conflicts.

Compliance or Networking Policies

Some organizations may have specific networking policies or compliance regulations that require running database services on custom ports.

3. How to change the default port in PostgreSQL

Overall, changing the port is a simple yet effective way to manage database configurations, improve security, and run multiple instances smoothly. However, there may be times when we need to change this port. For example

• We might run multiple database instances on the same machine.
• We may want to enhance security by using a non-default port.

4. Steps to Change the Default Port in PostgreSQL

Locate the postgresql.conf File

The postgresql.conf file is usually located in the data directory of PostgreSQL installation. The exact location depends on how PostgreSQL was installed, but we can easily find the file by following these steps:

• Linux/Unix Systems: Common paths include /var/lib/pgsql/data or /etc/postgresql/12/main. We can also search for it using the following command:

sudo find / -name postgresql.conf

• Windows Systems: The data directory is usually located in C:\Program Files\PostgreSQL\version\data.

Edit the postgresql.conf File

Open the postgresql.conf file in a text editor of your choice:

• Linux/Unix:

sudo nano /etc/postgresql/12/main/postgresql.conf

• Windows: Use a text editor like Notepad to open the file.

Look for the following line in the configuration file. The line is commented out by default with a # symbol, meaning the default port (5432) is used

#port = 5432

Uncomment the line and change the port number to your desired value. For example, to change the port to 5433, modify the line as follows then save and close the file.

port = 5433

Restart the PostgreSQL Service

For the changes to take effect, we must restart the PostgreSQL server.

• On Linux/Unix:

sudo systemctl restart postgresql

• On Windows:

Open Services (press Windows + R, type services.msc, and hit enter). Find PostgreSQL in the list, right-click it, and select Restart. Alternatively, we can restart the PostgreSQL service from the command line:

pg_ctl -D /path/to/data/directory restart

• On macOS (using Homebrew):

brew services restart postgresql

Confirm the Port Change

To verify that PostgreSQL is listening on the new port, we can use the following command:

• On Linux/Unix:

sudo netstat -plnt | grep postgres

We should see an output like this, indicating that PostgreSQL is now listening on the new port (5433 in this example):

tcp   0   0 0.0.0.0:5433      0.0.0.0:*         LISTEN      12345/postgres

4. Testing the Connection to the New Port

After changing the port, we need to verify that PostgreSQL is accepting connections on the new port.

Connecting Using psql

We can use the psql command-line tool to connect to our PostgreSQL server. To test the new port, specify the port with the -p option:

psql -h localhost -U postgres -p 5433 -d mydatabase

Replace localhost with the PostgreSQL server’s address, postgres with the PostgreSQL username, and mydatabase with the database we want to connect to.

Connecting Using a PostgreSQL GUI

If we are using a PostgreSQL GUI like pgAdmin or DBeaver, we can specify the new port in the connection settings:

In pgAdmin, go to Servers > Properties > Connection tab, and enter the new port number (e.g., 5433).

In DBeaver, when setting up a new connection, specify the port under General > Port.

5. Advanced Configuration: Using Multiple Ports

We can run multiple PostgreSQL instances on the same machine by assigning different ports to each instance. This is particularly useful in development and testing environments where we want to simulate a multi-database setup on a single host.

• Running Multiple Instances: Create a New Data Directory for the second instance (let’s call it pg2)

mkdir /var/lib/pgsql/pg2

• Initialize the new database:

sudo -u postgres initdb -D /var/lib/pgsql/pg2

• Edit the postgresql.conf file in the new data directory (/var/lib/pgsql/pg2):

sudo nano /var/lib/pgsql/pg2/postgresql.conf

• Start the new instance: You can now connect to the new instance on port 5434.

sudo pg_ctl -D /var/lib/pgsql/pg2 start

Automating PostgreSQL Instances

To run multiple PostgreSQL instances automatically, we can set up separate service files for each instance (for example, postgresql-5433 and postgresql-5434), each pointing to its respective configuration and data directory.

6. Troubleshooting Common Issues

Port Already in Use

If we attempt to start PostgreSQL and get an error like:

Could not bind to address '127.0.0.1:5433': Address already in use

This means another process is already using the port. You can use netstat or lsof to identify the process using that port:

sudo netstat -tuln | grep 5433

or

sudo lsof -i :5433

Once you identify the process, you can either stop the service or choose a different port for PostgreSQL.

Connection Refused

If you get a connection refused error after changing the port, make sure:

• The PostgreSQL service has been restarted properly.
• The firewall is not blocking the new port.
• The postgresql.conf file is configured with the correct IP addresses (check the listen_addresses parameter, which should be set to * to allow all addresses or localhost for local connections).

Authentication Errors

After changing the port, make sure we update the connection string or any client configurations to use the new port. For example, in a connection string:

postgresql://user:password@localhost:5433/mydatabase

7. Best Practices

Use Ports Above 1024

When choosing a new port for PostgreSQL, ensure that the port number is above 1024 (the range reserved for system processes and root services). This avoids conflicts with other system processes.

Update Firewall and Security Rules

If we change the port, ensure that firewall rules, security groups, and access control lists (ACLs) are updated to allow traffic on the new port.

Monitor Logs

After changing the port, monitor the PostgreSQL logs (/var/log/postgresql/postgresql.log) for any issues that may arise related to connection or performance.

Conclusion

Changing the default port in PostgreSQL is a simple process that can help improve security, resolve port conflicts, and enable running multiple instances on the same server.

Whether we are working in development, staging, or production environments, customizing the PostgreSQL port is a valuable tool for managing your database configurations.

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

Overview

Managing ports is vital for system security and resource management. This tutorial delves into using a custom port for PostgreSQL to enhance your database configuration management skills.

Introduction

PostgreSQL is a powerful open-source object-relational database system. By default, PostgreSQL listens to port 5432. However, there might be scenarios where running it on a different port is necessary, such as when avoiding conflicts with other services or for security reasons. In this tutorial, we’ll explore how to configure PostgreSQL to use a custom port, and discuss how to connect to the database once the changes are made.

Changing the PostgreSQL Port

To specify a custom port, you must edit the postgresql.conf file, which contains configuration settings for your PostgreSQL server. This file is typically located in PostgreSQL’s data directory. The exact path differs depending on your operating system and the method you used to install PostgreSQL.

# On a Linux system default installation, the file might be found at:
/etc/postgresql/12/main/postgresql.conf

# For a MacOS installation with Homebrew, it could be accessed with:
/usr/local/var/postgres/postgresql.conf

# On Windows, you may find it at:
C:\Program Files\PostgreSQL\12\data\postgresql.conf

Once located, open the file with your favorite text editor. To specify a new port, locate the line starting with port = 5432, and change the number to the desired port number.

# Change the following line in postgresql.conf
port = 5432
# To your custom port number
port = 5433

After saving the file, you need to restart the PostgreSQL service for the change to take effect. The method of restarting the service will again depend on your system.

# On a Linux system with systemd, you would use:
sudo systemctl restart postgresql

# On MacOS with Homebrew:
brew services restart postgresql

# On Windows, you can use the Services management console or Command Prompt:
NET STOP postgresql-x64-12 && NET START postgresql-x64-12

Connecting to PostgreSQL on a Custom Port

Once your PostgreSQL server is running on a new port, you should update your connection string in your applications or tools that access the database. Here, we use the psql command-line utility as an example.

# Connecting via psql to the server on custom port 5433
psql -h localhost -p 5433 -U yourUsername dbName

Most libraries and ORMs require you to update the database configuration to specify the port. Here’s an example in Python using the psycopg2 library:

import psycopg2

connection = psycopg2.connect(
    dbname="yourDatabase",
    user="yourUsername",
    password="yourPassword",
    host="localhost",
    port="5433"
)

Advanced Configuration (with Docker)

More complex setups may require running multiple instances of PostgreSQL or dynamic port assignments. Running several instances might be beneficial for separating different environments or for specialized configurations. For such situations, creating multiple service files or using a configuration manager might be necessary.

Additionally, when running PostgreSQL within containerized environments, such as Docker, the port configuration is typically handled in the docker-compose.yml or Dockerfile, mapping the internal port used by the container to any port on the host system.

# Example docker-compose.yml using PostgreSQL on a custom port
services:
  db:
    image: postgres
    ports:
      - "5433:5432"
    environment:
      POSTGRES_PASSWORD: yourPassword

Security Considerations

When configuring a custom port for PostgreSQL, consider the security implications. Ensure that the chosen port is properly secured by your firewall and is not publicly accessible unless absolutely necessary. Configuring these security aspects is beyond the scope of this tutorial, but you should be mindful of them when modifying the default PostgreSQL configuration.

Conclusion

Customizing the PostgreSQL port can be a useful skill in any database administrator’s toolbox. We explored how to modify PostgreSQL’s configuration to listen on a custom port, how to connect to it after the change, and took a glimpse at more advanced setups. With appropriate security measures and system management, adapting PostgreSQL to your environment needs becomes a seamless process.