Как изменить порт pgadmin - Исправление ошибок и поиск оптимальных решений проблем

Use the Server dialog to describe a connection to a server. Note: you must
ensure that the pg_hba.conf file of the server from which you are connecting
allows connections from the host of the client.

Use the fields in the General tab to identify the server:

Use the Name field to add a descriptive name for the server; the name
specified will be displayed in the Browser tree control.
Use the drop-down list box in the Server group field to select the parent
node for the server; the server will be displayed in the Browser tree
control within the specified group.
Use the color-picker in the Background field to specify the background
color for the server.
Use the color-picker in the Foreground field to specify the foreground
color for the server.
If the Connect now? checkbox is checked, the client will attempt a
connection to the server upon completion of the dialog; this is the default
If the Shared? switch is moved to Yes then that server can be
shared with all the other users. This option is available only to admin users.
For more information on users see User Management Dialog.
Users can access the shared servers with some restrictions — the following
operations on shared servers are not permitted:
- Delete the server
- Rename the server
- Rename the group server
- Change of host, port, and maintenance database
Please note that once the server is shared, it’s icon is changed in the
browser tree.
Provide a comment about the server in the Comments field.

Click the Connection tab to continue.

Use the fields in the Connection tab to configure a connection:

Specify the IP address of the server host, or the fully qualified domain
name in the Host name/address field. If you provide a unix domain socket,
the directory name must begin with a “/”.
Enter the listener port number of the server host in the Port field. The
default is 5432.
Use the Maintenance database field to specify the name of the initial
database to which the client will connect. If you will be using pgAgent or
adminpack objects, the pgAgent schema and adminpack objects should be
installed on that database.
Use the Username field to specify the name of a role that will be used when
authenticating with the server.
When Kerberos authentication? is set to True, pgAdmin will try to connect
the PostgreSQL server using Kerberos authentication.
Use the Password field to provide a password that will be supplied when
authenticating with the server.
Check the box next to Save password? to instruct pgAdmin to save the
password for future use. Use
Clear Saved Password to remove the saved
password.
Use the Role field to specify the name of a role that has privileges that
will be conveyed to the client after authentication with the server. This
selection allows you to connect as one role, and then assume the permissions
of this specified role after the connection is established. Note that the
connecting role must be a member of the role specified.
Use the Service field to specify the service name. For more information,
see
Section 33.16 of the Postgres documentation.

Click the Parameters tab to continue.

Use the fields in the Parameters tab to configure a connection:

Click on the + button to add a new parameter. Some of the parameters are:

Host address using this field to specify the host IP address may save time
by avoiding a DNS lookup on connection, but it may be useful to specify both
a host name and address when using Kerberos, GSSAPI, or SSPI authentication
methods, as well as for verify-full SSL certificate verification.
Password File field to specify the location of a password file
(.pgpass). A .pgpass file allows a user to login without providing a password
when they connect. For more information, see
Section 33.15 of the Postgres documentation.
Connection timeout field to specify the maximum wait for connection,
in seconds. Zero or not specified means wait indefinitely. It is not
recommended to use a timeout of less than 2 seconds. By default it is set to
10 seconds.
SSL mode field to select the type of SSL connection the server should use.
For more information about using SSL encryption, see
Section 33.18 of the Postgres documentation.

If pgAdmin is installed in Server mode (the default mode), you can use the
platform-specific File manager dialog to upload files that support SSL
encryption to the server. To access the File manager dialog, click the
icon that is located to the right of each of the following fields.

Client certificate field to specify the file containing the client
SSL certificate. This file will replace the default
~/.postgresql/postgresql.crt if pgAdmin is installed in Desktop mode, and
<STORAGE_DIR>/<USERNAME>/.postgresql/postgresql.crt if pgAdmin is installed
in Web mode. This parameter is ignored if an SSL connection is not made.
Client certificate key field to specify the file containing the
secret key used for the client certificate. This file will replace the
default ~/.postgresql/postgresql.key if pgAdmin is installed in Desktop
mode, and <STORAGE_DIR>/<USERNAME>/.postgresql/postgresql.key if pgAdmin
is installed in Web mode. This parameter is ignored if an SSL connection is
not made.
Root certificate field to specify the file containing the SSL
certificate authority. This file will replace the default
~/.postgresql/root.crt. This parameter is ignored if an SSL connection is
not made.
Certificate revocation list field to specify the file containing
the SSL certificate revocation list. This list will replace the default list,
found in ~/.postgresql/root.crl. This parameter is ignored if an SSL
connection is not made.
SSL compression? is set to True, data sent over SSL connections will
be compressed. The default value is False (compression is disabled). This
parameter is ignored if an SSL connection is not made.

Warning

In Server mode, certificates, private keys, and the revocation list
are stored in the per-user file storage area on the server, which is owned
by the user account under which the pgAdmin server process is run. This
means that administrators of the server may be able to access those files;
appropriate caution should be taken before choosing to use this feature.

Click the SSH Tunnel tab to continue.

Use the fields in the SSH Tunnel tab to configure SSH Tunneling:

You can use the “SSH Tunnel” tab to connect pgAdmin (through an intermediary
proxy host) to a server that resides on a network to which the client may
not be able to connect directly.

Set “Use SSH tunneling” to Yes to specify that pgAdmin should use an SSH
tunnel when connecting to the specified server.
Specify the name or IP address of the SSH host (through which client
connections will be forwarded) in the Tunnel host field.
Specify the port of the SSH host (through which client connections will be
forwarded) in the Tunnel port field.
Specify the name of a user with login privileges for the SSH host in the
Username field.
Specify the type of authentication that will be used when connecting to the
SSH host in the Authentication field:
- Select the Password option to specify that pgAdmin will use a password
  for authentication to the SSH host. This is the default.
- Select the Identity file to specify that pgAdmin will use a private key
  file when connecting.
If the SSH host is expecting a private key file for authentication, use the
Identity file field to specify the location of the key file.
If the SSH host is expecting a password of the user name or an identity file
if being used, use the Password field to specify the password.
Check the box next to Save password? to instruct pgAdmin to save the
password for future use. Use
Clear SSH Tunnel Password to remove the saved
password.

Click the Advanced tab to continue.

Use the fields in the Advanced tab to configure a connection:

Use the DB restriction field to provide a SQL restriction that will be used
against the pg_database table to limit the databases that you see. For
example, you might enter: live_db test_db so that only live_db and test_db
are shown in the pgAdmin browser. Separate entries with a comma or tab as you
type.
Use the Password exec command field to specify a shell command to be executed
to retrieve a password to be used for SQL authentication. The stdout of the
command will be used as the SQL password. This may be useful when the password
should be generated as a transient authorization token instead of providing a
password when connecting in PAM authentication scenarios.
Use the Password exec expiration field to specify a maximum age, in seconds,
of the password generated with a Password exec command. If not specified,
the password will not expire until your pgAdmin session does.
Zero means the command will be executed for each new connection or reconnection that is made.
If the generated password is not valid indefinitely, set this value to slightly before it will expire.

Note

The password file option is only supported when pgAdmin is using libpq
v10.0 or later to connect to the server.

Note

The Password exec option is only supported when pgAdmin is run in desktop mode.

Click the Save button to save your work.
Click the Close button to exit without saving your work.
Click the Reset button to return the values specified on the Server dialog
to their original condition.

Clear Saved Passwords

Источник

My PostgreSQL default port is 5432, I want to change default port by using command line (in Windows 7).

I saw this solution before: https://stackoverflow.com/questions/187438/want-to-change-pgsql-port

I tried running the following command, but it failed:

postgres --p 5431

Could someone tell me how to change port in using command line?

asked May 3, 2013 at 5:23

Since you’re on Windows and are probably starting PostgreSQL as a Windows service, you’ll need to edit postgresql.conf (inside your data directory) to set the new port there, then restart the postgresql service using the Services control panel or (as an Administrator) the net service command.

You can use a programmatic text editing tool to change postgresql.conf if you need to automate this. On a Linux/unix box you’d use a tool like sed or awk, but on Windows I imagine you’ll be using VBS, powershell, or an add-on tool like Perl.

You’ll need to change the connection settings in tools like PgAdmin-III to use the new port.

If you were starting PostgreSQL manually using pg_ctl you could set the PGPORT environment variable or pass a config option on the command line. This won’t work when you’re launching it as a Windows service.

answered May 3, 2013 at 6:51

Craig RingerCraig Ringer

53.8k3 gold badges147 silver badges185 bronze badges

go to ..PostgreSQL9.0data and open the file postgresql.conf in text editor/notepad
search for port parameter .eg: port = 5433
edit this to your port number.
go to run type services.msc and restart postgresql service.

you can check wether the parameter is set or not via query tool. just execute show port query. it will display your current port number. If are using pgAdmin to connect to db make sure you changed your port number in connection settings.

answered May 3, 2013 at 9:14

A solution that does not require any text replacement or similar techniques is to configure the different port in a separate config file, then append an include directive to the postgresql.conf file.

e.g.

echo port = 5431 > pathtodatadircustom.conf
echo include custom.conf>> pathtodatadirpostgresql.conf

^{(Of course pathtodatadir needs to be replaced with the correct path)}

The first echo command creates a new file with just the port configuration. The second one appends an include directive to the existing postgres config file. As the «later» settings override «earlier» settings in the file, any port configuration done in postgresql.conf will be overwritten with the one in the custom config file.

answered May 8, 2019 at 9:30

Источник

My PostgreSQL default port is 5432, I want to change default port by using command line (in Windows 7).

I saw this solution before: https://stackoverflow.com/questions/187438/want-to-change-pgsql-port

I tried running the following command, but it failed:

postgres --p 5431

Could someone tell me how to change port in using command line?

asked May 3, 2013 at 5:23

You’ll need to change the connection settings in tools like PgAdmin-III to use the new port.

answered May 3, 2013 at 6:51

Craig RingerCraig Ringer

53.8k3 gold badges147 silver badges185 bronze badges

go to ..PostgreSQL9.0data and open the file postgresql.conf in text editor/notepad
search for port parameter .eg: port = 5433
edit this to your port number.
go to run type services.msc and restart postgresql service.

answered May 3, 2013 at 9:14

e.g.

echo port = 5431 > pathtodatadircustom.conf
echo include custom.conf>> pathtodatadirpostgresql.conf

^{(Of course pathtodatadir needs to be replaced with the correct path)}

answered May 8, 2019 at 9:30

Источник

Introduction to PostgreSQL Port

PostgreSQL provides a facility to make connections between client and server using port number, IP address, and the default port number of PostgreSQL is 5432. The Port number, IP address are the database administrative part. It plays an important role in the database management system to establish a connection with remote users, which we called TCP/IP connection; it uses a local socket for configuration. With the help of port number and IP address, we are able to connect multiple connections in the network to remote access server and database. It also includes security for windows operating systems.

Syntax:

Port = Port Number listen_addresses = ‘ip address’

Explanation:

In the above syntax, where Port Number is specified Port number that we need to change, and listen_connection is used to IP address.
With the help of the above syntax, we can change the Port number and IP.

How does Port work in PostgreSQL?

We must install PostgreSQL in our system.
We require basic knowledge about PostgreSQL.
We must require knowledge about configuration files.
We just need basic knowledge about TCP/IP.

Basic term related to the port number:

1. listen_addresses

listen_address specifies TCP/IP single or multiple address on the server to establish a connection from the client site. If the listen_address value is *, that means it is available for all IP addresses. The IP address 0.0.0.0 allows listing all IPv4 addresses and :: allows all IPv6 addresses. If the above two addresses are empty, then the server is not able to listen to any IP.

2. Port

The default port number is 5432. TCP port uses this port number to listen to all IP addresses on the server.

3. max_connections

It determines the maximum number of connections on the database server. Typically 100 connections may allow, but sometimes it may be less. It depends on your system’s kernel setting. If we increase the size of the number of users, then it is harmful to PostgreSQL.

4. superuser_reserved_connections

It is used to check the number of connection slots available for PostgreSQL superusers. The max size of connection is 3 that means a superuser is allowed.

5. What is a requirement of port number

Suppose we need to access data in the database on client location, which we call remote users on various locations in the network at that time we need Port Number.

6. How to check the port IP address

Suppose we need to check the Port number and IP address; at that time, we use the following command for the Windows operating system as well as Linux.

Code:

grep port number

Explanation:

In the above command where grep is commanded along with the port number of the current system. It shows the IP address.
Another way to check Port Number and IP address are we can directly open postgresql.conf file and check Port number and IP.
Illustrate the end result of the above declaration by using the use of the following snapshot.

7. Edit postgresql.conf

Suppose we need to take the remote access of the database at that time we need to make some changes in the postgresql.conf file. Illustrate the end result of the above declaration by using the use of the following snapshot.

Explanation:

In the above snapshot, the listen_addresses we mentioned are 1.1.1.1, and the port number is by default 5432. Here we can change the port number and IP address as per requirement.

8. How to restart PostgreSQL

After editing of listen to addresses and IP, we must restart the postgresql service. On the Linux system, we directly start the postgresql service through the terminal. And windows, we need to search services in the search option of the start menu. Illustrate the end result of the above declaration by using the use of the following snapshot.

Restart

9. Configuration of pg_hba.conf for authentication

In this file, we allow remote access using IP4 and IP5 IP addresses to access databases and all users with the help of the md5 protocol.

10. Windows firewall port

At this point, we can run the PostgreSQL database on a windows server with a firewall.

In the above scenario, you can just turn off the firewall.

Steps for turn off windows firewall are as follow:

Open Control Panel.
Select System and Security.
Select Windows Firewall.
Select Turn Windows Firewall on or off option.
Save setting and exit.

Another option for setup of firewall are as follows:

Open Control Panel.
Select System and Security.
Select Windows Firewall.
Select Advanced Setting.
Right-click on Inbound Rule.
Select a new.
Select Port in Rule Type.
Select TCP program and enter port number (here, you can use the default port number of any other user-defined).
Next, select the Allow the connection.
Then select when we need to apply the rule (Domain, Private and Public checked all three options).
Then assign any name that you want.
Finally, click on finish.

Remotely Access the User and Database

Suppose we need to access databases and users on different systems. If we don’t have another machine, then we can execute on pgAdmin4. When we complete the above setting, then open pgAdmin4 and follow the following steps.

First, create a new server (right-click on the server and create the server).
Then give the name to the server.
Then click on the connection and give the remote machine’s IP address that we also need to give a password and save the setting.
Then see the new server on the left side of the window.
Select created server and open with entering a password.

Conclusion

From the above article, we saw basic terms of networking as well as related to port numbers. From this article, we are able to access the remote machine, and we also see how we can do setup or configuration of PostgreSQL Port on windows operating systems. The same thing is applicable for Linux operating systems. Finally, from this article, we are able to do the configuration for the remote machine.

Содержание

SSH туннель к PostgreSQL
Прямое подключение к PostgreSQL
Частые вопросы

Статья на других языках:
🇺🇸 – How to Setup Remote Access to PostgreSQL Database from Windows
🇪🇸 – Cómo configurar el acceso remoto a la base datos PostgreSQL desde Windows

SSH туннель к PostgreSQL

Подключение к серверу PostgreSQL с использованием SSH туннеля. При выборе такого типа подключения никаких дополнительных настроек на сервере PostgreSQL не требуется.

Настройка pgAdmin на Windows 10:

Запускаем pgAdmin
Создаем новое подключение к серверу: Object -> Create -> Server;
Вкладка General:
1. Name: название сервера (произвольное);
Вкладка Connection:
1. Host Name: localhost;
2. Port: 5432;
3. Maintenance database: mybase;
4. Username: postgres;
Вкладка SSH Tunnel:
1. Use SSH tunneling: Yes;
2. Tunnel host: myserver-IP;
3. Tunnel port: 58222;
4. Username: root;
5. Authentication: Identity file;
6. Identity file: path_key.

💡 В качестве ключа указываем приватный ключ id_rsa из Настройка SSH сертификатов на сервере. Как изменить порт SSH в Частые вопросы.

Настройки pgAdmin для подключения через SSH туннель

Прямое подключение к PostgreSQL

Для настройки прямого подключения к PostgreSQL вносим изменения в файлы конфигурации postgresql.conf и pg_hba.conf

Настройка PostgreSQL

postgresql.conf

Файл postgresql.conf находится в папке установки PostgreSQL.

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

Раскомментируем или добавим строку:

listen_addresses = '*'

Мы разрешили прослушивание запросов от всех IP-адресов.

💡 Возможные варианты:
listen_addresses = '0.0.0.0' чтобы слушать все IPv4;
listen_addresses = '::' чтобы слушать все IPv6;
listen_addresses = 'your.host.ip.adress' определенный адрес или список адресов через запятую.

pg_hba.conf

Файл pg_hba.conf находится в папке установки PostgreSQL.

Открываем на редактирование:

sudo nano /etc/postgresql/13/main/pg_hba.conf

Добавляем запись в секцию # IPv4 local connections:

host     mybd     postgres     41.223.232.15/32     md5

Запись разрешает подключение к БД mybd пользователю postgres с IP адресом 41.223.232.15, используя пароль.

После изменения файлов конфигурации, перезапустите службу PostgreSQL.

systemctl restart postgresql

Настройка pgAdmin

Настройка прямого подключения к базе данных PostgreSQL через интернет или локальную сеть используя pgAdmin.

Запустите pgAdmin
Создаем новое подключение к серверу: Object -> Create -> Server;
Вкладка General:
1. Name: название сервера (произвольное);
Вкладка Connection:
1. Host Name: RemoteServerIP;
2. Port: 5432;
3. Maintenance database: mybase;
4. Username: postgres.

Частые вопросы

PostgreSQL как узнать расположение файлов конфигурации?

ps aux | grep postgres | grep — -D

PostgreSQL как узнать порт?

grep -H ‘^port’ /etc/postgresql/*/main/postgresql.conf

SSH как узнать или изменить порт подключения?

Файл /etc/ssh/sshd_config , строка port.

Как настроить удаленное подключение к БД PostgreSQL из Windows обсуждалось в этой статье. Я надеюсь, что теперь вы сможете настроить pgAdmin для подключения к PostgreSQL (прямое подключение или используя SSH туннель). Однако, если вы столкнетесь с каким-то проблемами при настройке сервера или pgAdmin, не стесняйтесь написать в комментариях. Я постараюсь помочь.

Источник

Introduction to PostgreSQL Port

How does Port work in PostgreSQL?

1. listen_addresses

2. Port

3. max_connections

4. superuser_reserved_connections

5. What is a requirement of port number

6. How to check the port IP address

7. Edit postgresql.conf

8. How to restart PostgreSQL

9. Configuration of pg_hba.conf for authentication

10. Windows firewall port

Remotely Access the User and Database

Conclusion

Recommended Articles

Содержание

SSH туннель к PostgreSQL

Прямое подключение к PostgreSQL

Настройка PostgreSQL

postgresql.conf

pg_hba.conf

Настройка pgAdmin

Частые вопросы