I have PSQL running, and am trying to get a perl application connecting to the database. Is there a command to find the current port and host that the database is running on?
lospejos
1,9683 gold badges19 silver badges34 bronze badges
asked Apr 8, 2011 at 17:13
SELECT *
FROM pg_settings
WHERE name = 'port';
answered Apr 8, 2011 at 17:21
4
This command will give you postgres port number
conninfo
If Postgres is running on a Linux server, you can also use the following command
sudo netstat -plunt |grep postgres
OR (if it comes as postmaster)
sudo netstat -plunt |grep postmaster
and you will see something similar as this
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 140/postgres
tcp6 0 0 ::1:5432 :::* LISTEN 140/postgres
In this case, the port number is 5432 which is also the default port number
credit: link
answered Jun 24, 2016 at 10:36
KhainoKhaino
3,5461 gold badge25 silver badges36 bronze badges
2
The default PostgreSQL port is 5432. The host that the database is operating on should have been provided by your hosting provider; I’d guess it would be the same host as the web server if one wasn’t specified. Typically this would be configured as localhost, assuming your web server and database server are on the same host.
answered Apr 8, 2011 at 17:18
Brad KochBrad Koch
18.7k18 gold badges108 silver badges136 bronze badges
5
select inet_server_addr(); gives you the ip address of the server.
answered Sep 17, 2012 at 7:05
KaarelKaarel
3,9581 gold badge18 silver badges16 bronze badges
3
This is non-sql method. Instructions are given on the image itself. Select the server that you want to find the info about and then follow the steps.
answered Jun 5, 2014 at 15:43
bonCodigobonCodigo
14.1k1 gold badge47 silver badges87 bronze badges
0
select inet_server_addr( ), inet_server_port( );
answered Jun 2, 2016 at 19:34
gerardwgerardw
5,56244 silver badges38 bronze badges
2
select inet_server_port(); gives you the port of the server.
answered Apr 10, 2014 at 11:20
AndromidaAndromida
1,0751 gold badge11 silver badges28 bronze badges
1
The postgresql port is defined in your postgresql.conf file.
For me in Ubuntu 14.04 it is: /etc/postgresql/9.3/main/postgresql.conf
Inside there is a line:
port = 5432
Changing the number there requires restart of postgresql for it to take effect.
answered Apr 6, 2015 at 21:11
Eric LeschinskiEric Leschinski
142k95 gold badges408 silver badges332 bronze badges
1
From the terminal you can do:
conninfo
I would suggest reading a documentation on their exhaustive list of all commands using:
?
answered Nov 7, 2019 at 6:39
From the terminal you can simply do a «postgres list clusters»:
pg_lsclusters
It will return Postgres version number, cluster names, ports, status, owner, and the location of your data directories and log file.
answered Jan 31, 2021 at 7:14
You can use the command in psql conninfo
you will get You are connected to database "your_database" as user "user_name" on host "host_name" at port "port_number".
answered Jul 26, 2019 at 12:35
IntraIntra
1672 silver badges7 bronze badges
To find the port number you can run this command (assuming you are on localhost)
select setting from pg_settings where name='port';
answered Aug 26, 2021 at 21:02
A-SharabianiA-Sharabiani
16.9k16 gold badges111 silver badges127 bronze badges
SELECT CURRENT_USER usr, :'HOST' host, inet_server_port() port;
This uses psql’s built in HOST variable, documented here
And postgres System Information Functions, documented here
answered Mar 28, 2018 at 10:52
1
service postgresql status
returns:
10/main (port 5432): online
I’m running Ubuntu 18.04
answered Apr 29, 2019 at 16:55
go to the «Terminal» and just type
service postgres status
In the results you can get the port details
In my case it’s running on port «5432» (default).
I’m using CentOS 7.Hope this helps.
answered Nov 8, 2017 at 7:43
Because you said you (yourself) have postgresql running, I’ll assume:
- you’re on Linux,
- have at least one account with superuser privileges and/or can access the postgres role, and
- (just for fun) you need to access both values within a single transaction from within the database itself
/* SQL CODE */
CREATE TEMP TABLE tmp ( hostname text, port bigint ) ON COMMIT DROP;
COPY tmp FROM PROGRAM $pgm$ printf "$HOSTNAMEt$(i=1 && until [[ "$(psql -U postgres -p $i -qt -c "SELECT 'true'" 2>/dev/null | sed -e '$d' | xargs | tr -d "n")" == "true" ]]; do i=$(($i+1)) && if [ $i == "65535" ]; then break ; fi ; done && echo $i)"$pgm$ ( format 'text', delimiter 't' );
SELECT host, port FROM tmp;
will give you both, executing the $pgm$-delimited code as a shell script and returning the values to the server-side COPY API’s stdin. Unfortunately, this method needs a table target to invoke the server-side shell.
If you need to be able to call without a temp table, i.e. as a function invocation, try implementing the above shell in the plsh language.
answered Jun 29, 2022 at 0:42
I think PostgreSQL didn’t provide an in-built function to get the hostname of server so we might need to write an extension to get the information from server.
I found there is a PostgreSQL extension pg-hostname which can get the hostname from server.
When we have installed the extension we can enable that and query information by inet_server_port & hostname function.
CREATE EXTENSION hostname;
SELECT hostname(),inet_server_port();
answered Aug 5, 2022 at 3:52
D-ShihD-Shih
44.2k6 gold badges29 silver badges50 bronze badges
An addition to the @a_horse_with_no_name answer.
To get the hostname:
SELECT boot_val,reset_val FROM pg_settings WHERE name='listen_addresses';;
answered Aug 17, 2022 at 15:48
t7et7e
2321 gold badge2 silver badges9 bronze badges
I use such a solution. No plugins required. Temporary tables are not needed. Only for unix.
select pg_read_file('/etc/hostname') as hostname, setting as port from pg_settings where name='port';
answered Oct 31, 2022 at 17:33
ArtyArty
5791 gold badge8 silver badges17 bronze badges
Right click your SQL server. Mine is PostgresSQL 13 and select properties -> connection. This has:
- host name
- port ID
- Username.
answered Nov 24, 2022 at 1:45
JQTsJQTs
1302 silver badges9 bronze badges
Connecting to a Database
psql is a regular PostgreSQL client application. In order to connect to a database you need to know the name of your target database, the host name and port number of the server, and what user name you want to connect as. psql can be told about those parameters via command line options, namely -d, -h, -p, and -U respectively. If an argument is found that does not belong to any option it will be interpreted as the database name (or the user name, if the database name is already given). Not all of these options are required; there are useful defaults. If you omit the host name, psql will connect via a Unix-domain socket to a server on the local host, or via TCP/IP to localhost on machines that don’t have Unix-domain sockets. The default port number is determined at compile time. Since the database server uses the same default, you will not have to specify the port in most cases. The default user name is your operating-system user name, as is the default database name. Note that you cannot just connect to any database under any user name. Your database administrator should have informed you about your access rights.
When the defaults aren’t quite right, you can save yourself some typing by setting the environment variables PGDATABASE, PGHOST, PGPORT and/or PGUSER to appropriate values. (For additional environment variables, see Section 34.15.) It is also convenient to have a ~/.pgpass file to avoid regularly having to type in passwords. See Section 34.16 for more information.
An alternative way to specify connection parameters is in a conninfo string or a URI, which is used instead of a database name. This mechanism give you very wide control over the connection. For example:
$psql "service=myservice sslmode=require"$psql postgresql://dbmaster:5433/mydb?sslmode=require
This way you can also use LDAP for connection parameter lookup as described in Section 34.18. See Section 34.1.2 for more information on all the available connection options.
If the connection could not be made for any reason (e.g., insufficient privileges, server is not running on the targeted host, etc.), psql will return an error and terminate.
If both standard input and standard output are a terminal, then psql sets the client encoding to “auto”, which will detect the appropriate client encoding from the locale settings (LC_CTYPE environment variable on Unix systems). If this doesn’t work out as expected, the client encoding can be overridden using the environment variable PGCLIENTENCODING.
Entering SQL Commands
In normal operation, psql provides a prompt with the name of the database to which psql is currently connected, followed by the string =>. For example:
$ psql testdb
psql (15.1)
Type "help" for help.
testdb=>
At the prompt, the user can type in SQL commands. Ordinarily, input lines are sent to the server when a command-terminating semicolon is reached. An end of line does not terminate a command. Thus commands can be spread over several lines for clarity. If the command was sent and executed without error, the results of the command are displayed on the screen.
If untrusted users have access to a database that has not adopted a secure schema usage pattern, begin your session by removing publicly-writable schemas from search_path. One can add options=-csearch_path= to the connection string or issue SELECT pg_catalog.set_config('search_path', '', false) before other SQL commands. This consideration is not specific to psql; it applies to every interface for executing arbitrary SQL commands.
Whenever a command is executed, psql also polls for asynchronous notification events generated by LISTEN and NOTIFY.
While C-style block comments are passed to the server for processing and removal, SQL-standard comments are removed by psql.
Advanced Features
Variables
psql provides variable substitution features similar to common Unix command shells. Variables are simply name/value pairs, where the value can be any string of any length. The name must consist of letters (including non-Latin letters), digits, and underscores.
To set a variable, use the psql meta-command set. For example,
testdb=> set foo bar
sets the variable foo to the value bar. To retrieve the content of the variable, precede the name with a colon, for example:
testdb=> echo :foo
bar
This works in both regular SQL commands and meta-commands; there is more detail in SQL Interpolation, below.
If you call set without a second argument, the variable is set to an empty-string value. To unset (i.e., delete) a variable, use the command unset. To show the values of all variables, call set without any argument.
Note
The arguments of set are subject to the same substitution rules as with other commands. Thus you can construct interesting references such as set :foo 'something' and get “soft links” or “variable variables” of Perl or PHP fame, respectively. Unfortunately (or fortunately?), there is no way to do anything useful with these constructs. On the other hand, set bar :foo is a perfectly valid way to copy a variable.
A number of these variables are treated specially by psql. They represent certain option settings that can be changed at run time by altering the value of the variable, or in some cases represent changeable state of psql. By convention, all specially treated variables’ names consist of all upper-case ASCII letters (and possibly digits and underscores). To ensure maximum compatibility in the future, avoid using such variable names for your own purposes.
Variables that control psql‘s behavior generally cannot be unset or set to invalid values. An unset command is allowed but is interpreted as setting the variable to its default value. A set command without a second argument is interpreted as setting the variable to on, for control variables that accept that value, and is rejected for others. Also, control variables that accept the values on and off will also accept other common spellings of Boolean values, such as true and false.
The specially treated variables are:
AUTOCOMMIT-
When
on(the default), each SQL command is automatically committed upon successful completion. To postpone commit in this mode, you must enter aBEGINorSTART TRANSACTIONSQL command. Whenoffor unset, SQL commands are not committed until you explicitly issueCOMMITorEND. The autocommit-off mode works by issuing an implicitBEGINfor you, just before any command that is not already in a transaction block and is not itself aBEGINor other transaction-control command, nor a command that cannot be executed inside a transaction block (such asVACUUM).Note
In autocommit-off mode, you must explicitly abandon any failed transaction by entering
ABORTorROLLBACK. Also keep in mind that if you exit the session without committing, your work will be lost.Note
The autocommit-on mode is PostgreSQL‘s traditional behavior, but autocommit-off is closer to the SQL spec. If you prefer autocommit-off, you might wish to set it in the system-wide
psqlrcfile or your~/.psqlrcfile. COMP_KEYWORD_CASE-
Determines which letter case to use when completing an SQL key word. If set to
lowerorupper, the completed word will be in lower or upper case, respectively. If set topreserve-lowerorpreserve-upper(the default), the completed word will be in the case of the word already entered, but words being completed without anything entered will be in lower or upper case, respectively. DBNAME-
The name of the database you are currently connected to. This is set every time you connect to a database (including program start-up), but can be changed or unset.
ECHO-
If set to
all, all nonempty input lines are printed to standard output as they are read. (This does not apply to lines read interactively.) To select this behavior on program start-up, use the switch-a. If set toqueries, psql prints each query to standard output as it is sent to the server. The switch to select this behavior is-e. If set toerrors, then only failed queries are displayed on standard error output. The switch for this behavior is-b. If set tonone(the default), then no queries are displayed. ECHO_HIDDEN-
When this variable is set to
onand a backslash command queries the database, the query is first shown. This feature helps you to study PostgreSQL internals and provide similar functionality in your own programs. (To select this behavior on program start-up, use the switch-E.) If you set this variable to the valuenoexec, the queries are just shown but are not actually sent to the server and executed. The default value isoff. ENCODING-
The current client character set encoding. This is set every time you connect to a database (including program start-up), and when you change the encoding with
encoding, but it can be changed or unset. ERROR-
trueif the last SQL query failed,falseif it succeeded. See alsoSQLSTATE. FETCH_COUNT-
If this variable is set to an integer value greater than zero, the results of
SELECTqueries are fetched and displayed in groups of that many rows, rather than the default behavior of collecting the entire result set before display. Therefore only a limited amount of memory is used, regardless of the size of the result set. Settings of 100 to 1000 are commonly used when enabling this feature. Keep in mind that when using this feature, a query might fail after having already displayed some rows.Tip
Although you can use any output format with this feature, the default
alignedformat tends to look bad because each group ofFETCH_COUNTrows will be formatted separately, leading to varying column widths across the row groups. The other output formats work better. HIDE_TABLEAM-
If this variable is set to
true, a table’s access method details are not displayed. This is mainly useful for regression tests. HIDE_TOAST_COMPRESSION-
If this variable is set to
true, column compression method details are not displayed. This is mainly useful for regression tests. HISTCONTROL-
If this variable is set to
ignorespace, lines which begin with a space are not entered into the history list. If set to a value ofignoredups, lines matching the previous history line are not entered. A value ofignorebothcombines the two options. If set tonone(the default), all lines read in interactive mode are saved on the history list.Note
This feature was shamelessly plagiarized from Bash.
HISTFILE-
The file name that will be used to store the history list. If unset, the file name is taken from the
PSQL_HISTORYenvironment variable. If that is not set either, the default is~/.psql_history, or%APPDATA%postgresqlpsql_historyon Windows. For example, putting:set HISTFILE ~/.psql_history-:DBNAME
in
~/.psqlrcwill cause psql to maintain a separate history for each database.Note
This feature was shamelessly plagiarized from Bash.
HISTSIZE-
The maximum number of commands to store in the command history (default 500). If set to a negative value, no limit is applied.
Note
This feature was shamelessly plagiarized from Bash.
HOST-
The database server host you are currently connected to. This is set every time you connect to a database (including program start-up), but can be changed or unset.
IGNOREEOF-
If set to 1 or less, sending an EOF character (usually Control+D) to an interactive session of psql will terminate the application. If set to a larger numeric value, that many consecutive EOF characters must be typed to make an interactive session terminate. If the variable is set to a non-numeric value, it is interpreted as 10. The default is 0.
Note
This feature was shamelessly plagiarized from Bash.
LASTOID-
The value of the last affected OID, as returned from an
INSERTorlo_importcommand. This variable is only guaranteed to be valid until after the result of the next SQL command has been displayed. PostgreSQL servers since version 12 do not support OID system columns anymore, thus LASTOID will always be 0 followingINSERTwhen targeting such servers. LAST_ERROR_MESSAGELAST_ERROR_SQLSTATE-
The primary error message and associated SQLSTATE code for the most recent failed query in the current psql session, or an empty string and
00000if no error has occurred in the current session. ON_ERROR_ROLLBACK-
When set to
on, if a statement in a transaction block generates an error, the error is ignored and the transaction continues. When set tointeractive, such errors are only ignored in interactive sessions, and not when reading script files. When set tooff(the default), a statement in a transaction block that generates an error aborts the entire transaction. The error rollback mode works by issuing an implicitSAVEPOINTfor you, just before each command that is in a transaction block, and then rolling back to the savepoint if the command fails. ON_ERROR_STOP-
By default, command processing continues after an error. When this variable is set to
on, processing will instead stop immediately. In interactive mode, psql will return to the command prompt; otherwise, psql will exit, returning error code 3 to distinguish this case from fatal error conditions, which are reported using error code 1. In either case, any currently running scripts (the top-level script, if any, and any other scripts which it may have in invoked) will be terminated immediately. If the top-level command string contained multiple SQL commands, processing will stop with the current command. PORT-
The database server port to which you are currently connected. This is set every time you connect to a database (including program start-up), but can be changed or unset.
PROMPT1PROMPT2PROMPT3-
These specify what the prompts psql issues should look like. See Prompting below.
QUIET-
Setting this variable to
onis equivalent to the command line option-q. It is probably not too useful in interactive mode. ROW_COUNT-
The number of rows returned or affected by the last SQL query, or 0 if the query failed or did not report a row count.
SERVER_VERSION_NAMESERVER_VERSION_NUM-
The server’s version number as a string, for example
9.6.2,10.1or11beta1, and in numeric form, for example90602or100001. These are set every time you connect to a database (including program start-up), but can be changed or unset. SHOW_ALL_RESULTS-
When this variable is set to
off, only the last result of a combined query (;) is shown instead of all of them. The default ison. The off behavior is for compatibility with older versions of psql. SHOW_CONTEXT-
This variable can be set to the values
never,errors, oralwaysto control whetherCONTEXTfields are displayed in messages from the server. The default iserrors(meaning that context will be shown in error messages, but not in notice or warning messages). This setting has no effect whenVERBOSITYis set toterseorsqlstate. (See alsoerrverbose, for use when you want a verbose version of the error you just got.) SINGLELINE-
Setting this variable to
onis equivalent to the command line option-S. SINGLESTEP-
Setting this variable to
onis equivalent to the command line option-s. SQLSTATE-
The error code (see Appendix A) associated with the last SQL query’s failure, or
00000if it succeeded. USER-
The database user you are currently connected as. This is set every time you connect to a database (including program start-up), but can be changed or unset.
VERBOSITY-
This variable can be set to the values
default,verbose,terse, orsqlstateto control the verbosity of error reports. (See alsoerrverbose, for use when you want a verbose version of the error you just got.) VERSIONVERSION_NAMEVERSION_NUM-
These variables are set at program start-up to reflect psql‘s version, respectively as a verbose string, a short string (e.g.,
9.6.2,10.1, or11beta1), and a number (e.g.,90602or100001). They can be changed or unset.
SQL Interpolation
A key feature of psql variables is that you can substitute (“interpolate”) them into regular SQL statements, as well as the arguments of meta-commands. Furthermore, psql provides facilities for ensuring that variable values used as SQL literals and identifiers are properly quoted. The syntax for interpolating a value without any quoting is to prepend the variable name with a colon (:). For example,
testdb=>set foo 'my_table'testdb=>SELECT * FROM :foo;
would query the table my_table. Note that this may be unsafe: the value of the variable is copied literally, so it can contain unbalanced quotes, or even backslash commands. You must make sure that it makes sense where you put it.
When a value is to be used as an SQL literal or identifier, it is safest to arrange for it to be quoted. To quote the value of a variable as an SQL literal, write a colon followed by the variable name in single quotes. To quote the value as an SQL identifier, write a colon followed by the variable name in double quotes. These constructs deal correctly with quotes and other special characters embedded within the variable value. The previous example would be more safely written this way:
testdb=>set foo 'my_table'testdb=>SELECT * FROM :"foo";
Variable interpolation will not be performed within quoted SQL literals and identifiers. Therefore, a construction such as ':foo' doesn’t work to produce a quoted literal from a variable’s value (and it would be unsafe if it did work, since it wouldn’t correctly handle quotes embedded in the value).
One example use of this mechanism is to copy the contents of a file into a table column. First load the file into a variable and then interpolate the variable’s value as a quoted string:
testdb=>set content `cat my_file.txt`testdb=>INSERT INTO my_table VALUES (:'content');
(Note that this still won’t work if my_file.txt contains NUL bytes. psql does not support embedded NUL bytes in variable values.)
Since colons can legally appear in SQL commands, an apparent attempt at interpolation (that is, :name, :'name', or :"name") is not replaced unless the named variable is currently set. In any case, you can escape a colon with a backslash to protect it from substitution.
The :{? special syntax returns TRUE or FALSE depending on whether the variable exists or not, and is thus always substituted, unless the colon is backslash-escaped.name}
The colon syntax for variables is standard SQL for embedded query languages, such as ECPG. The colon syntaxes for array slices and type casts are PostgreSQL extensions, which can sometimes conflict with the standard usage. The colon-quote syntax for escaping a variable’s value as an SQL literal or identifier is a psql extension.
Prompting
The prompts psql issues can be customized to your preference. The three variables PROMPT1, PROMPT2, and PROMPT3 contain strings and special escape sequences that describe the appearance of the prompt. Prompt 1 is the normal prompt that is issued when psql requests a new command. Prompt 2 is issued when more input is expected during command entry, for example because the command was not terminated with a semicolon or a quote was not closed. Prompt 3 is issued when you are running an SQL COPY FROM STDIN command and you need to type in a row value on the terminal.
The value of the selected prompt variable is printed literally, except where a percent sign (%) is encountered. Depending on the next character, certain other text is substituted instead. Defined substitutions are:
%M-
The full host name (with domain name) of the database server, or
[local]if the connection is over a Unix domain socket, or[local:, if the Unix domain socket is not at the compiled in default location./dir/name] %m-
The host name of the database server, truncated at the first dot, or
[local]if the connection is over a Unix domain socket. %>-
The port number at which the database server is listening.
%n-
The database session user name. (The expansion of this value might change during a database session as the result of the command
SET SESSION AUTHORIZATION.) %/-
The name of the current database.
%~-
Like
%/, but the output is~(tilde) if the database is your default database. %#-
If the session user is a database superuser, then a
#, otherwise a>. (The expansion of this value might change during a database session as the result of the commandSET SESSION AUTHORIZATION.) %p-
The process ID of the backend currently connected to.
%R-
In prompt 1 normally
=, but@if the session is in an inactive branch of a conditional block, or^if in single-line mode, or!if the session is disconnected from the database (which can happen ifconnectfails). In prompt 2%Ris replaced by a character that depends on why psql expects more input:-if the command simply wasn’t terminated yet, but*if there is an unfinished/* ... */comment, a single quote if there is an unfinished quoted string, a double quote if there is an unfinished quoted identifier, a dollar sign if there is an unfinished dollar-quoted string, or(if there is an unmatched left parenthesis. In prompt 3%Rdoesn’t produce anything. %x-
Transaction status: an empty string when not in a transaction block, or
*when in a transaction block, or!when in a failed transaction block, or?when the transaction state is indeterminate (for example, because there is no connection). %l-
The line number inside the current statement, starting from
1. %digits-
The character with the indicated octal code is substituted.
%:name:-
The value of the psql variable
name. See Variables, above, for details. %`command`-
The output of
command, similar to ordinary “back-tick” substitution. %[…%]-
Prompts can contain terminal control characters which, for example, change the color, background, or style of the prompt text, or change the title of the terminal window. In order for the line editing features of Readline to work properly, these non-printing control characters must be designated as invisible by surrounding them with
%[and%]. Multiple pairs of these can occur within the prompt. For example:testdb=> set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '
results in a boldfaced (
1;) yellow-on-black (33;40) prompt on VT100-compatible, color-capable terminals. %w-
Whitespace of the same width as the most recent output of
PROMPT1. This can be used as aPROMPT2setting, so that multi-line statements are aligned with the first line, but there is no visible secondary prompt.
To insert a percent sign into your prompt, write %%. The default prompts are '%/%R%x%# ' for prompts 1 and 2, and '>> ' for prompt 3.
Note
This feature was shamelessly plagiarized from tcsh.
Command-Line Editing
psql uses the Readline or libedit library, if available, for convenient line editing and retrieval. The command history is automatically saved when psql exits and is reloaded when psql starts up. Type up-arrow or control-P to retrieve previous lines.
You can also use tab completion to fill in partially-typed keywords and SQL object names in many (by no means all) contexts. For example, at the start of a command, typing ins and pressing TAB will fill in insert into . Then, typing a few characters of a table or schema name and pressing TAB will fill in the unfinished name, or offer a menu of possible completions when there’s more than one. (Depending on the library in use, you may need to press TAB more than once to get a menu.)
Tab completion for SQL object names requires sending queries to the server to find possible matches. In some contexts this can interfere with other operations. For example, after BEGIN it will be too late to issue SET TRANSACTION ISOLATION LEVEL if a tab-completion query is issued in between. If you do not want tab completion at all, you can turn it off permanently by putting this in a file named .inputrc in your home directory:
$if psql set disable-completion on $endif
(This is not a psql but a Readline feature. Read its documentation for further details.)
The -n (--no-readline) command line option can also be useful to disable use of Readline for a single run of psql. This prevents tab completion, use or recording of command line history, and editing of multi-line commands. It is particularly useful when you need to copy-and-paste text that contains TAB characters.
9 ответов
Порт PostgreSQL по умолчанию — 5432. Хост, на котором работает база данных, должен быть предоставлен вашим хостинг-провайдером; Я предполагаю, что это будет тот же хост, что и веб-сервер, если он не указан. Обычно это будет настроено как localhost, если ваш веб-сервер и сервер базы данных находятся на одном хосте.
Brad Koch
08 апр. 2011, в 18:07
Поделиться
SELECT *
FROM pg_settings
WHERE name = 'port';
a_horse_with_no_name
08 апр. 2011, в 18:04
Поделиться
Эта команда даст вам номер порта postgres
conninfo
В случае, если postgres запущен на сервере Linux, вы также можете использовать следующую команду
sudo netstat -plunt |grep postgres
и вы увидите что-то подобное, как это
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 140/postgres
tcp6 0 0 ::1:5432 :::* LISTEN 140/postgres
, в этом случае номер порта равен 5432, который также является номером порта по умолчанию
кредит ссылка
Khai No
24 июнь 2016, в 10:37
Поделиться
select inet_server_addr(); предоставляет IP-адрес сервера.
Kaarel Kitsemets
17 сен. 2012, в 08:16
Поделиться
select inet_server_port(); предоставляет порт сервера.
Andromida
10 апр. 2014, в 11:28
Поделиться
Порт postgresql определен в вашем файле postgresql.conf.
Для меня в Ubuntu 14.04 это: /etc/postgresql/9.3/main/postgresql.conf
Внутри есть строка:
port = 5432
Для изменения числа требуется перезапуск postgresql, чтобы он вступил в силу.
Eric Leschinski
06 апр. 2015, в 22:49
Поделиться
Это не-sql-метод. Инструкции даются на самом изображении. Выберите сервер, на котором вы хотите найти информацию, а затем выполните действия.
bonCodigo
05 июнь 2014, в 15:57
Поделиться
select inet_server_addr( ), inet_server_port( );
gerardw
02 июнь 2016, в 21:28
Поделиться
перейдите в «Терминал» и просто введите
service postgres status
В результатах вы можете получить порт подробнее 
В моем случае он работает на порту «5432» (по умолчанию).
Я использую CentOS 7.Hope это помогает.
Rahal Kanishka
08 нояб. 2017, в 08:14
Поделиться
Ещё вопросы
- 1Запутался в argparse
- 0установка Python MySQL не удалось
- 1отменить диалог через 3 секунды — продолжает сбой моего приложения после многократного использования
- 0DATE_FORMAT не работает в операторе вставки
- 0jQuery Mobile настройки Иконки для кнопок
- 1Как войти в сеть Wi-Fi с помощью JavaScript
- 0Получить значение строки запроса из URL внутри большей строки с помощью PHP
- 1Как исправить ошибки печати в коде
- 1DependencyProperty объекта UserControl равен нулю, если элемент UserControl имеет DataContext.
- 0Проблема с добавлением двух полиномов, построенных как связанный список c ++
- 1Ошибка при вызове метода setRequestedOrientation ()
- 1Метод в Vue запускается дважды по клику
- 1Нужно ли синхронизировать доступ к аргументам и локальным методам при одновременном вызове метода?
- 1Сокет-сервер без подключения
- 1Создание TODO для базового лагеря возвращает 403 запрещенных (restsharp, asp.net mvc, basecamp api)
- 0Предотвратить автоматическое обновление с помощью php?
- 0Выставленный литерал объекта службы Angular не обновляется?
- 1Удалить жесты из RatingBar
- 0facebook: Как я могу получить ссылки на оригинальные фотографии из сообщений публичной группы
- 1RuntimeError: потоки могут быть запущены только один раз, когда веб-сервер Python Tkinter
- 1Вычислить среднее значение для каждой строки тензора в TensorFlow
- 0Повторяйте вложенный цикл, если результат не тот, который я хотел
- 0C ++, оператор valgrind new (без знака int)
- 1C # Пользовательский тип данных с «выбором»
- 1Операция со списком файлов не удалась после нескольких раундов
- 0Подсчет записей в MySQL с помощью сложного оператора HAVING
- 0Несколько аргументов для данных в BOOST_PP_REPEAT
- 0AngularJS заказать к дате
- 0php — проверка xml против xsd
- 0Найти пары в таблице с двумя столбцами и объединить в таблицу с тремя столбцами
- 0Angularjs выбирает «поддельную» модель обновления
- 0Отключить видимость элемента, изменить innerHTML и вернуть обратно
- 1Как принять Ctrl + Enter в качестве возврата при обработке события Enter для текстового поля в WPF?
- 0Директива не отображается на странице после изменения состояния, но после прямой загрузки
- 0Редактирование html темы tumblr для удаления некоторых элементов
- 0Последняя версия Wokrbench требует старой версии Mysql при попытке экспорта БД, но не может понизить версию Mysql (Wampserver) (со скриншотом с ошибкой)
- 1Okta Sign In Widget перенаправить обратно на исходную страницу после входа в систему
- 1Как добавить элементы из строки, добавленной из консоли, в список в Java
- 0Загрузить данные JSON в функцию «Готов к документу»
- 0как работает указатель при перемещении на массив целых
- 0извлекать невыпуклую оболочку из результата суммы Минковского с помощью наддува
- 1Как установить компоненты платформы Android в автономном режиме?
- 0Переверните строку, используя C и встроенную сборку
- 1Повторное использование кода Java через Inheritance, когда используются дженерики (перечислите, в частности)?
- 1Добавление данных файла WAV в Excel
- 1GeoServer — DWITHIN неправильно фильтрует точки
- 0C ++ Выход из цикла после нажатия определенной клавиши
- 1Как кластеризовать несколько строк с хотя бы одним и тем же значением в python?
- 0Ошибка 32-битного изображения OpenCV вне границ?
- 1Limelight Video Player || Как воспроизвести видео HTML5 с ним
Настройка удаленного подключения к БД PostgreSQL 13 на сервере Ubuntu 18 LTS из ОС Windows 10 утилиты pgAdmin 4, двумя способами: подключение с помощью SSH туннеля и прямое подключение к серверу PostgreSQL.
💡 Я рекомендую использовать подключение через SSH туннель, простое в настройке и безопасное. При использовании SSH туннеля, порт PostgreSQL не открывается для внешних подключений.
Для использования SSH туннеля, необходимо настроить SSH сертификаты входа на Ubuntu.
pgAdmin — самая популярная и многофункциональная платформа для администрирования и разработки с открытым исходным кодом для PostgreSQL, самой совершенной базы данных с открытым исходным кодом в мире.
Официальный сайт pgAdmin
Узнать расположение файлов конфигурации PostgreSQL: postgresql.conf, pg_hba.conf.
ps aux | grep postgres | grep -- -DУзнать порт PostgreSQL командой:
grep -H '^port' /etc/postgresql/*/main/postgresql.conf
В примере, файл конфигурации PostgreSQL располагается по пути /etc/postgresql/13/main/postgresql.conf, порт подключения 5432.
Содержание
- 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:
- Name: название сервера (произвольное);
- Вкладка Connection:
- Host Name: localhost;
- Port: 5432;
- Maintenance database: mybase;
- Username: postgres;
- Вкладка SSH Tunnel:
- Use SSH tunneling: Yes;
- Tunnel host: myserver-IP;
- Tunnel port: 58222;
- Username: root;
- Authentication: Identity file;
- Identity file: path_key.
💡 В качестве ключа указываем приватный ключ id_rsa из Настройка SSH сертификатов на сервере. Как изменить порт 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:
- Name: название сервера (произвольное);
- Вкладка Connection:
- Host Name: RemoteServerIP;
- Port: 5432;
- Maintenance database: mybase;
- 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, не стесняйтесь написать в комментариях. Я постараюсь помочь.
I know that by default PostgreSQL listens on port 5432, but what is the command to actually determine PostgreSQL’s port?
Configuration: Ubuntu 9.10 with PostgreSQL 8.4
voretaq7
79.5k17 gold badges130 silver badges214 bronze badges
asked Mar 1, 2010 at 13:40
Matthew RankinMatthew Rankin
1,1555 gold badges15 silver badges32 bronze badges
lsof and nmap are solutions, but they’re not installed by default. What you want is netstat(8).
sudo netstat -plunt |grep postgres
answered Mar 1, 2010 at 15:25
3
The PostgreSQL utility pg_lsclusters shows information about the configuration and status of all clusters, including the port number.
$ pg_lsclusters
Version Cluster Port Status Owner Data directory Log file
8.4 main 5433 online postgres /var/lib/postgresql/8.4/main /var/log/postgresql/postgresql-8.4-main.log
This also has the advantage of not requiring ‘sudo’ privileges to run.
On Debian and Ubuntu systems, the pg_lsclusters command is provided by the package postgresql-common, which should be installed by default with the postgresql server.
answered Jul 12, 2012 at 18:43
1
If you want to do it from inside the database, just do «SHOW port». But that assumes you’ve been able to connect to it, at least locally…
answered Mar 1, 2010 at 14:34
If you are searching on the local machine, I would use the lsof command to check for the port postgresql is using
lsof -p <postgres_process_id>
answered Mar 1, 2010 at 13:50
DominikDominik
2,21814 silver badges9 bronze badges
1
I have machines with multiple postgres instances running — and so I also have the issue of trying to match up the correct database with each port. I tend to do:
$ ps aux | grep postgres | grep -v 'postgres:'
And then, for each of instances returned look for the directory (-D argument) and:
$ sudo grep port $DIR/postgresql.conf
answered Mar 1, 2010 at 14:04
Joe H.Joe H.
1,90712 silver badges12 bronze badges
Here’s one solution that I’ve found:
sudo apt-get install nmap
sudo nmap localhost | grep postgresql
If you’re wanting to search a non-local machine, just change localhost to the server’s IP address.
answered Mar 1, 2010 at 13:41
Matthew RankinMatthew Rankin
1,1555 gold badges15 silver badges32 bronze badges
I think you need to define what you’re trying to achieve better. Do you just want to know if anything is listening on a certain point? If PostgreSQL is listening on a given port? If PostgreSQL is running and actually accepting connections? If you can connect to PostgreSQL, authenticate successfully and issue queries?
One option is to invoke psql to connect to it and check the result code. Do not attempt to parse the output text, since that’s subject to translation into different languages.
Better, use the client library for the language of your choice — psycopg2 for Python, PgJDBC for Java, the Pg gem for Ruby, DBD::Pg for Perl, nPgSQL for C#, etc. This is the approach I’d recommend. The SQLSTATE or exception details from any connection error will tell you more about why the connection failed — you’ll be able to tell the difference between the server not listening, authentication failure, etc this way. For example, in Python:
import psycopg2
try:
conn = psycopg2.connect("host=localhost dbname=postgres")
conn.close()
except psycopg2.OperationalError as ex:
print("Connection failed: {0}".format(ex))
There are exception details in ex.pgcode (the SQLSTATE) to tell you more about errors that’re generated server-side, like authentication failures; it’ll be empty for client-side errors.
If you just want to see if something is listening on a given IP and TCP port, you can use netcat (*nix only), or a simple script in the language of your choice that creates a socket and does a connect() then closes the socket if it gets a successful response. For example, the following trivial Python script:
import socket
s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
try:
s.connect(('localhost',5432))
s.close()
except socket.error as ex:
print("Connection failed with errno {0}: {1}".format(ex.errno, ex.strerror))
The same approach applies in any programming language, just the details of the socket library and error handling vary.
For some purposes it can also be useful to use the netstat tool to passively list which processes are listening on which network sockets. The built-in netstat on Windows is pretty brain-dead so you have to do more parsing of the output than with netstat for other platforms, but it’ll still do the job. The presence of a socket in netstat doesn’t mean that connecting to it will succeed, though; if the process has failed in some way that leaves it broken but still running (stuck in an infinite loop, blocked by a debugger, SIGSTOPed, etc) then it won’t respond to an actual connection attempt.
I think you need to define what you’re trying to achieve better. Do you just want to know if anything is listening on a certain point? If PostgreSQL is listening on a given port? If PostgreSQL is running and actually accepting connections? If you can connect to PostgreSQL, authenticate successfully and issue queries?
One option is to invoke psql to connect to it and check the result code. Do not attempt to parse the output text, since that’s subject to translation into different languages.
Better, use the client library for the language of your choice — psycopg2 for Python, PgJDBC for Java, the Pg gem for Ruby, DBD::Pg for Perl, nPgSQL for C#, etc. This is the approach I’d recommend. The SQLSTATE or exception details from any connection error will tell you more about why the connection failed — you’ll be able to tell the difference between the server not listening, authentication failure, etc this way. For example, in Python:
import psycopg2
try:
conn = psycopg2.connect("host=localhost dbname=postgres")
conn.close()
except psycopg2.OperationalError as ex:
print("Connection failed: {0}".format(ex))
There are exception details in ex.pgcode (the SQLSTATE) to tell you more about errors that’re generated server-side, like authentication failures; it’ll be empty for client-side errors.
If you just want to see if something is listening on a given IP and TCP port, you can use netcat (*nix only), or a simple script in the language of your choice that creates a socket and does a connect() then closes the socket if it gets a successful response. For example, the following trivial Python script:
import socket
s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
try:
s.connect(('localhost',5432))
s.close()
except socket.error as ex:
print("Connection failed with errno {0}: {1}".format(ex.errno, ex.strerror))
The same approach applies in any programming language, just the details of the socket library and error handling vary.
For some purposes it can also be useful to use the netstat tool to passively list which processes are listening on which network sockets. The built-in netstat on Windows is pretty brain-dead so you have to do more parsing of the output than with netstat for other platforms, but it’ll still do the job. The presence of a socket in netstat doesn’t mean that connecting to it will succeed, though; if the process has failed in some way that leaves it broken but still running (stuck in an infinite loop, blocked by a debugger, SIGSTOPed, etc) then it won’t respond to an actual connection attempt.