How to change the listening port for PostgreSQL Database

How to change the listening port for PostgreSQL Database

Category : How-to

Get Social!

The default TCP port for PostgreSQL is usually 5432, however this can easily be changed in the postgresql.conf configuration file, which is the main configuration file for the database server.

In addition to a TCP listening port, PostgreSQL will also a local socket if the server is running in a Linux/ Unix environment. A local socket is the prefered method of connecting to a database as it removes much of the overhead of creating a TCP connection and transferring data. This comes with the limitation that it can only be used if the application accessing the database is on the same machine. In larger or highly available systems this may not be possible.

A TCP connection is the only option of connecting to your PostgreSQL database server from a remote machine. It incurs a small penalty over a local socket and therefore slightly higher latencies and is limited by the network bandwidth available. PostgreSQL Server can be configured to use a local socket, TCP connections or both.

be editing the postgresql.conf file for the following sections however the location of the file is different depending on OS and PostgreSQL version. In Linux you can use the following command to find the file:

locate postgresql.conf
/etc/postgresql/9.5/main/postgresql.conf

As you can see, the server version is included in the file path so you’ll need to check that before trying to open it.

On Windows it’s usually C:\Program Files\PostgreSQL\9.5\data but again, you’ll have to take the version into account.

I’m using Linux for this example, but adjust the following steps to suit your environment. Open the file in your favourite editor.

vi /etc/postgresql/9.5/main/postgresql.conf

Configuring local socket use (Linux/ Unix only)

The unix_socket_directory option indicates the filesystem path to the location of the directory you’d like to hold your socket. Specify a filesystem directory path, usually /var/run/postgresql/ and the socket will be created when the server next starts. Remove or comment (#) the line to disable socket access.

unix_socket_directory= /var/run/postgresql/

Restart the server for the changes to take effect.

service postgresql restart

Setting or changing the TCP port

The port option sets the PostgreSQL server port number that will be used when listening for TCP/ IP connections. The default port number is 5432 but you can change it as required. Use the port option with the
listen_addresses option to control the interface where the port will be listening. Use ‘*’ to listen on all interfaces on the host, specify a single host name or IP address to listen on a single interface, or separate several hostname or IP addresses by space (such as ‘10.10.0.1 10.11.0.1’). Omit both of these options to disable TCP/ IP connections.

port = 2345
listen_addresses='*'

Restart the server for the changes to take effect.

service postgresqlrestart

Change Listening Port of MySQL or MariaDB Server

Category : How-to

Get Social!

mysql-logoThe MySQL and MariaDB server both use a file called my.cnf for parameters that are used to configure the server. This is where the port number and, if you use it, the local socket can be configured. The default port number for both MySQL and MariaDB is 3306 but you can change it as required.

A local socket is the prefered method of connecting to a database as it removes much of the overhead of creating a TCP connection and transferring data. This comes with the limitation that it can only be used if the application accessing the database is on the same machine. In larger or highly available systems this may not be possible.

A TCP connection is the only option of connecting to your MySQL or MariaDB database from a remote machine. It incurs a small penalty over a local socket and therefore slightly higher latencies. MySQL server and MariaDB can be configured to use a local socket, TCP connections or both.

We’ll be editing the my.cnf file for the following sections. Open the file in your favourite editor.

vi /etc/mysql/my.cnf

Configuring local socket use

The socket option indicates the filesystem path to the location of the socket you’d like to use. Specify a filesystem path, usually /var/run/mysql/mysqld.sock and the socket will be created when the server next starts. Remove or comment (#) the line to disable socket access.

socket = /var/run/mysqld/mysqld.sock

Restart the server for the changes to take effect.

service mysql restart

Setting or changing the TCP port

The port option sets the MySQL or MariaDB server port number that will be used when listening for TCP/ IP connections. The default port number is 3306 but you can change it as required. Use the port option with the bind option to control the interface where the port will be listening. Use 0.0.0.0 to listen on all IP addresses on the host, or specify a single one directly to listen on a single interface. Omit both of these options to disable TCP/ IP connections.

port = 1234
bind = 10.10.10.10

Restart the server for the changes to take effect.

service mysql restart

Visit our advertisers

Quick Poll

How many Proxmox servers do you work with?

Visit our advertisers