Pgadmin hostname address что указывать

Настройка СУБД PostgreSQL

После установки СУБД postgreSQL необходимо создать БД для работы с ПАУ, а так же, настроить доступ к БД и серверу.

Важно: следует понимать, что параметры настройки будут отличаться от тех, которые приняты в разных компаниях и должны быть одобрены системным администратором, отвечающим за обслуживание сети и сервера

Ниже приведены типовые настройки для доступа к СУБД PosgtreSQL

Установка pgAdmin

1. Необходимо скачать утилиту для конфигурирования и настройки СУБД PostgreSQL с официального сайта
2. Установить утилиту можно как на локальный ПК, так и на сервер под управлением ОС Windows
3. Запустить pgAdmin

Если вы используете Internet Explorer и у вас проблемы с пользовательским интерфейсом pgAdmin вам нужно сделать следующее:

1. Перейти в трей на панели задач Windows

2. Правой кнопкой мыши нажать на иконку запущенного pgAdmin

3. Выбрать пункт Copy server URL

4. Открыть любой другой браузер (не Internet Explorer)

5. Вставить ссылку в адресную строку

Подключение к СУБД PostgreSQL

1. Подключение к серверу
2. Задать имя сервера На вкладке General в поле Name указать имя сервера (любое)

3. Задать адрес для подключения

На вкладке Connection указать параметры подключения:

• Если СУБД установлена на удаленном ПК или сервере, то в поле Hostname/address указать адрес или имя ПК/сервера, например: 192.168.0.123 или company-server;
• Если СУБД установлена на локальный ПК, то в поле Hostname/address следует указать имя сервера localhost.

4. Задать порт для подключения

На вкладке Connection указать параметры подключения:

• Если СУБД установлена на удаленном ПК или сервере, то в поле Hostname/address указать адрес или имя ПК/сервера, например: 192.168.0.123 или company-server;
• Если СУБД установлена на локальный ПК, то в поле Hostname/address следует указать имя сервера localhost.

5. Указать пароль для пользователя

На стадии установки СУБД PostgreSQL было предложено ввести пароль суперпользователя и сейчас необходимо указать его в поле Password ;

6. Сохранить настройки с помощью кнопки Save

Создание базы данных

После настройки подключения к СУБД PostgreSQL необходимо создать БД, для этого:

1. В окне Browser выбрать сервер, к которому было настроено подключение;
2. В меню Object выбрать пункт Create -> Database ;
3. В появившемся окне, в поле Database необходимо ввести ama ;
4. Сохранить параметры базы данных с помощью кнопки Save
5. База данных создана!

Создание пользователя

1. Задать имя пользователя

2. Задать пароль пользователя

3. Задать права пользователя. На рисунке ниже изображён минимальный перечень прав для работы с базой данных ПАУ.

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

1) В списке баз данных выбрать нужную. Нажать правой кнопкой мыши, по ней, и выбрать пункт меню Properties.

2) Перейти на вкладку Default Priveleges

3) Нажать на «+» и выбрать пользователя, на которого настраиваются права

4) Задать права выбранного пользователя

Настройка прав пользователя через Grant Wizard

При добавлении новых пользователей в базу данных с уже сформированной структурой можно воспользоваться инструментом Grant Wizard, чтобы быстро настроить права нового пользователя для работы с множеством существующих объектов базы данных. Для этого нужно сделать следующее:

1. Выбрать базу данных, и открыть пункт tools в верхнем меню и выбрать инструмент GrantWizard

2. В окне инструмента GrantWizard на первом шаге необходимо выбрать объекты на которые будут выданы права. Проставить галочки нужно на все объекты и нажать Next

3. На втором шаге потребуется выбрать перечень операций над объектами, которые будут доступны пользователю. Для этого кликаем на +, чтобы добавить «привелегию» и задаём пользователя, из списка пользователей и ролей в колонке Grantee. В колонке Privileges выбираем перечень операций. Минимальный перечень для работы с базой ПАУ, указан на рисунке

4. На третьем шаге просто нажимаем кнопку Finish

Pgadmin hostname address что указывать

While working with databases, obtaining information about the hostname and port number is crucial. This is because a Postgres database requires a hostname and port number to establish a connection. Host names indicate the location of the database server, while port numbers indicate how we can access a database.

This write-up illustrates various methods of finding the hostname and port number in PostgreSQL.

How to Check/Find the Hostname and Port Number in PostgreSQL?

The below-listed methods will be discussed in this post to check the hostname and port number:

• Using \conninfo
• Using pg_settings
• Using “inet_server_addr()” and “inet_server_port()”
• Using “postgresql.conf” File

How to Check/Find the Hostname and Port Number Using \conninfo?

“\conninfo” is a meta-command that retrieves connection details, such as database name, user name, port number, and hostname. To utilize this command, open the psql utility, provide the login privileges, and execute the below-provided meta-command:

\conninfo

The following snippet demonstrates that we are connected to “localhost” at the default port, which is “5432”:

How to Check/Find the Port Number Using pg_settings?

Postgres provide a pre-defined view named «pg_settings» that keeps detailed information about the current configuration settings of the Postgres database server. Users can utilize this view to query the hostname and port number:

SELECT * FROM pg_settings WHERE name = 'port';

How to Check/Find the Hostname and “inet_server_addr()”?

PostgreSQL provides an inbuilt function named “inet_server_addr()” that retrieves the server’s IP address (hostname). To get the hostname using the inet_server_addr() function, query the following command:

SELECT inet_server_addr() AS hostname;

The stated function retrieves “::1”, which is equivalent to «127.0.0.1«:

How to Check/Find the Port Number Using “inet_server_port()”?

In Postgres, a built-in function named “inet_server_port()” is used to get the server’s port number. For this purpose, query the following command:

SELECT inet_server_port() AS portNumber;

The given function retrieves the server’s port number as «5432«:

How to Check/Find the Hostname and Port Number Using “postgresql.conf” File?

The «postgresql.conf» is a configuration file that assists Postgres in managing different settings and parameters for the database server. Execute the following command to see the location where the «postgresql.conf» file is located:

show config_file;

The below snippet shows that the configuration file is located at “C:\Program Files\PostgreSQL\15\data\postgresql.conf” path:

Navigate to the stated path, open the configuration file in any text editor, and scroll down a little bit to reach the “CONNECTIONS AND AUTHENTICATION” section:

The output snippet demonstrates that we are connected to “localhost” at the default port, which is “5432”.

In PostgreSQL, several methods are used to check/find the hostnames and port numbers, such as the “\conninfo” command, the “pg_settings” view, the “inet_server_addr()” and “inet_server_port()” functions, and the “postgresql.conf” file. This post has demonstrated all these methods with practical examples.

Server Dialog¶

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 Object Explorer.
• 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 Object Explorer 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 object explorer.

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 //.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 //.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.

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.

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.
• Use the Prepare threshold field to specify the number of times a query is executed before it is prepared. If it is set to 0, every query is prepared the first time it is executed. If it is set to blank, prepared statements are disabled on the connection. This is particularly useful with external connection poolers, such as PgBouncer, which is not compatible with prepared statements. Set this to blank in such cases.

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

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.

Connection with my db using pgAdmin4

I am study with this tutorial: https://www.youtube.com/watch?v=Ho3DKKopC2E&list=PLDmvslp_VR0xlwr5lAx2PDsZLu7oIOhpX&index=20 When I connect to db, I want to connect the db like the turorial: But I failed, because I don’t know how to do with it: Does anyone knows how to do with it?

• database-connection
• pgadmin-4

5,701 1 1 gold badge 28 28 silver badges 52 52 bronze badges
asked Jan 26, 2017 at 12:46
25.4k 28 28 gold badges 94 94 silver badges 167 167 bronze badges

4 Answers 4

Add new server steps:

1. Open pgAdmin4
2. Right click in «Servers» in Browser panel
3. Enter some meaningful name for Server eg: «PostgreSQL9.6»
4. Click on «Connection» tab
5. Enter «Host» (default is «localhost» if database is installed on your own local system otherwise IP address of system where PostgreSQL database is installed)
6. Enter «Port» (default port for PostgreSQL server is «5432»)
7. Enter «Maintenance Database» name (default is «postgres»)
8. Enter «User Name» (default user is «postgres»)
9. Enter «Password» (Password which you gave when you installed PostgreSQL database for «postgres» user)
10. Click on CheckBox «Save password?» to save your password in pgAdmin4 for future use.

Now as you can see, «Save» button gets enable in dialog, just click on it.

You will see that your server is now listed under «Servers» and you should be connected to your database.

7,253 6 6 gold badges 49 49 silver badges 61 61 bronze badges
answered Jan 30, 2017 at 5:23
5,701 1 1 gold badge 28 28 silver badges 52 52 bronze badges

You are getting error because You did not specified Name for server, Click on «General» tab provide name for your server like «PostgreSQL9.6»

Refer the pgAdmin4 documents:

While creating new Server you should have all the required parameters like,

Host (default is "localhost" if database is installed on local system), Port(default is "5432"), Maintenance Database Name (default is "postgres"), User Name (default is "postgres"), Password 

Without above required information you can’t connect to PostgreSQL database server.