Поменять порт postgresql linux

PostgreSQL: Change default port used by utilites like psql, createdb, etc

I have 2 instances of PostgreSQL running on some servers. One on the default port (5432) and the other instance on port 5433. Some users (and processes) only need access to the second instance (5433) and I’d like to set it up so that when those users use commands like psql or createdb in their shell it will automatically direct them to the right Postgres instance instead of them having to type -p 5433 along with every command. I tried to look this up on www.postgresql.org but was unable to find it. probably my fault. Anyone know how?

A few things have to be clarified here: what OS do users use, how does the network environment of PostgreSQL look like (well, is that on a different box from the one which the users log on). And how often so they createdb ?

Mac OS X Server. in this case the users are logged into (ssh) the machine. The version of PostgreSQL that came with the server is somewhat out of date and devoted to system services. I’ve installed another instance for user processes and Rails apps. We only createdb when creating a new Rails application so not too often but ever couple weeks. Using psql happens more often, which prompted this inquiry. Thanks for your help!

An idea (I don’t know whether you have the means to do this): for running patches against a test DB here, I use an alias like test_patch , which is really psql -h [some IP] -p 5432 -d testdb

Источник

PostgreSQL change default port

A port is a place to which other peripheral devices are attached. For each purpose, the port is different. A port is a communication endpoint physically, but if we talk about its software level, then in any operating system, it identifies the processes or the type of network services. Port is known as a transport port and has an address that is formed through a 16-bit number, called port number. Many protocols use these port numbers. TCP is a commonly used protocol. Similarly, in PostgreSQL, there is a specific port to which it listens. We will use a method to change the port of PostgreSQL in this guide.

Prerequisite

We have used the Linux operating system for this purpose. To implement this article on Linux, you need to create a Linux running environment. First, install a Virtual box on your system. Install the file of Ubuntu, and then install it on a Virtual machine. After installation, configure it. Create a user and apply a password required for the future authentication of the user. The user must have access privileges to work on the Linux terminal because we will work on the command-line interface.

Similarly, the other software needed is PostgreSQL. The user must have PostgreSQL installed on the Linux system.

Postgresql port

The process of changing the default port in PostgreSQL will be discussed here. By default, during installation, you have seen that the port is 5432 and is present in the configuration file.

Moreover, if PostgreSQL is running on Linux/Unix environment, it will also need a local socket in addition to a TCP listening port. For the connection to the database, a local socket is recommended as it takes less time than creating a connection of TCP and transferring data. But it has a disadvantage, as it is only favorable if the application that is accessing the database is running on the same machine. On the Linux operating system, you can use the command listed below.

# /etc/postgresql/ 9.5 /main/postgresql.conf

You can notice that the name of the server is mentioned in the path of the file. You can check it before opening it. There are a series of steps; you are required to follow them.

NOTE: we will change the PostgreSQL default port: 5432 to 5440

Step 1

The first step is to enlist the details of the port that is currently in use. And that is done by using a command on PostgreSQL. That will select all the records from the file pg_settings, where the name will equal the port.

The port number can be seen, and the source name is also written that is inside the configuration file.

Step 2

The second step is to check the information of the connection. This will reveal the information showing the name of the database as “Postgres” and having the host localhost with the port number “5432”.

Step 3

The very next step is to now show the configuration file. The port number is written inside the configuration file.

Step 4

Now we will display the path of the configuration file with the help of a grep command. The port number will be displayed. You will see the results of this command; a message along with the port number is displayed that if we change the port, the system will require a restart.

We will update the port now. Again, use the same command and, this time, enter the port number you want to replace with the previous one. We will add the new one that is ‘5440’.

After updating, PostgreSQL needs to get restarted. So we will restart the PostgreSQL service. First, we will stop it and restart again.

Step 5

The next step is to check whether the port is updated or not. Go to the Linux terminal and then use the command to open the PostgreSQL with a new port number.

To again enlist the details of the new port and to ensure the changes in it, we will enable the display on Postgresql.

This will show the description of the new port.

From the resultant value, you can see that the port number is changed from the default value 5432 to 5440.

Port 5432- already in use

When you are installing PostgreSQL for the first time on your system, the most common problem is that two or more different versions of the PostgreSQL server are running simultaneously on the system. But this is not mandatory. This problem can also occur if any other application is using port 5432. If you want to check which application is using this port, then use the below command

After running this, you have to enter a password, and then you will see all the applications that are currently running on that port. You will see the PostgreSQL currently running on the default port. The only method to get rid of this problem is to cancel all the PostgreSQL processes that are currently running. For this purpose, use the below command

The reason behind doing this step is that PostgreSQL is usually the only app that is interested in using port 5432. But if you come to know that there is some other application than the PostgreSQL running on port 5432, check the activity monitor and see which application is interrupting the PostgreSQL server and then close it from there.

Conclusion

This tutorial is written to target those end users that are interested in changing the default port to any other of their choice. Moreover, a solution to commonly existing errors is also given that usually occurs during PostgreSQL installation on the system. The step-by-step procedure of updating the port will help the user change the default port number of PostgreSQL with the new one.

About the author

Saeed Raza

Hello geeks! I am here to guide you about your tech-related issues. My expertise revolves around Linux, Databases & Programming. Additionally, I am practicing law in Pakistan. Cheers to all of you.

Источник

change pgsql port [duplicate]

I have currently an installed pgsql instance that is running on port 1486 . I want to change this port to 5433 , how should I proceed for this?

2 Answers 2

There should be a line in your postgresql.conf file that says:

The location of the file can vary depending on your install options. On Debian-based distros it is /etc/postgresql/8.3/main/

On Windows it is C:\Program Files\PostgreSQL\9.3\data

Don’t forget to sudo service postgresql restart for changes to take effect.

On Ubuntu (and possibly other Linuxes), you can use locate postgresql.conf to find the path(s) of this file.

depending on the version you have installed on ubuntu type sudo nano /etc/postgresql/x.x/main/postgresql.conf you will find an entry PORT = . change that with the one you want and save after that restart the server using sudo service postgresql restart

@Sören, postgresql.conf location depends on individual installation. If you know where your Postgres has installed its data (your database’s physical files), you can find it inside that folder. Some Unix distros have locate utility that let’s you find any archive (I know Ubuntu and CentOs have this utility). I have seen this file inside /home/postgresql , /var/lib/pgsql/data . inspect the script that’s used to run initdb and look for PGDATA variable.

If you are trying to launch psql you may run into problems since psql still defaults to 5432 . You may want to do psql —port 1486

Источник

How to change the listening port for PostgreSQL Database

How to change the listening port for PostgreSQL Database

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

Источник