PostgreSQL Beginner Guide

Configure remote access - listen address

By default after instalation and creting database cluster PostgreSQL will listner only on localhost. No remote access will be allowed.

PostgreSQL installation on Linux - with database creation

[postgres@postgres-lab ~]$ netstat -lptnu | grep post
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      1977/postmaster     
tcp6       0      0 ::1:5432                :::*                    LISTEN      1977/postmaster

To change listen address we have to configure parameter in postgresql.conf

Check PGDATA - after -D parameter:

[postgres@postgres-lab ~]$ ps aux | grep postgres
postgres  1977  0.0  2.5 286388 14864 ?        Ss   Jun28   0:02 /usr/pgsql-12/bin/postmaster -D /postgresql/data
postgres  1979  0.0  0.2 140768  1360 ?        Ss   Jun28   0:00 postgres: logger   
postgres  1981  0.0  0.5 286504  3028 ?        Ss   Jun28   0:00 postgres: checkpointer   
postgres  1982  0.0  0.2 286388  1696 ?        Ss   Jun28   0:03 postgres: background writer   
postgres  1983  0.0  0.9 286388  5676 ?        Ss   Jun28   0:03 postgres: walwriter   
postgres  1984  0.0  0.4 286924  2688 ?        Ss   Jun28   0:02 postgres: autovacuum launcher

Locate the file:

[postgres@postgres-lab ~]$ cd /postgresql/data/
[postgres@postgres-lab data]$ ls -lah postgresql.conf
-rw-------. 1 postgres postgres 26K Jun 28 21:44 postgresql.conf

Change in postgresql.conf parameter listen_addresses to your server IP or * to listen on all IP’s available on server:

[postgres@postgres-lab data]$ vi postgresql.conf

##------------------------------------------------------------------------------
## CONNECTIONS AND AUTHENTICATION
##------------------------------------------------------------------------------

## - Connection Settings -

listen_addresses = '*'          ## what IP address(es) to listen on;
                                        ## comma-separated list of addresses;
                                        ## defaults to 'localhost'; use '*' for all

Restart PostgreSQL to apply changes - you can do that with systemctl from root os user service or with pg_ctl -D PGDATA restart from postgres os user:

[root@postgres-lab ~]## systemctl restart postgresql-12.service

Check whre PostgreSQL is listening now:

[postgres@postgres-lab ~]$ netstat -lptnu | grep post
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      30161/postmaster    
tcp6       0      0 :::5432                 :::*                    LISTEN      30161/postmaster

Configure remote access - pg_hba.conf

PostgreSQL instance has got restricted access by pg_hba.conf file(host based authentication file).

We can provide in it information from which ADDRESS to which DATABASE on which USER by what METHOD we allow connecting. Additionaly we have to provide TYPE of connection.

This file resides in same place where postgresql.conf (we can alter this behavior by setting pg_hba parameter in postgresql.conf ):

[postgres@postgres-lab ~]$ cd /postgresql/data/

[postgres@postgres-lab data]$ vi pg_hba.conf
## TYPE  DATABASE        USER            ADDRESS                 METHOD

## "local" is for Unix domain socket connections only
local   all             all                                     trust
## IPv4 local connections:
host    all             all             127.0.0.1/32            trust
## IPv6 local connections:
host    all             all             ::1/128                 trust

Allowed TYPE 's:

local
host
hostssl
hostnossl
hostgssenc
hostnogssenc

With DATABASE we can specify database name or use special value sameuser if database name should be same as name of user that is connecting.

With USER we can specify user or role - role name should be preceded by + sign.

ADDRESS field could be - hostname, IP range in CIDR format or special words:

samehost
samenet

With METHOD field we can set one of authentication methods - most important ones are:

trust
reject
md5
password
ldap

In DATABASE and USER fields you can specify special word all if you don’t want to create any restrictions here.

There can be situation when we must use additional field named auth-options for specyfying details for example for hostssl connection type. This topic will be covered in another post.

Sample pg_hba record - allow all users connect to any DB from all IP addresses - only with password

Add in pg_hba.conf :

## Network access
host    all             all             0.0.0.0/0               md5

Reload(online operation) PostgreSQL that it can use pg_hba.conf changes:

[postgres@postgres-lab data]$ /usr/pgsql-12/bin/pg_ctl -D /postgresql/data reload
server signaled

Connecting to PostgreSQL

Local from server

It will work without password because we have trust in pg_hba.conf for local connections:

[postgres@postgres-lab ~]$ psql
psql (12.3)
Type "help" for help.

Remote machine

[postgres@postgres-lab data]$ psql -h 10.128.0.2 -p 5432
Password for user postgres:
psql (12.3)
Type "help" for help.

postgres=##

We can also use parameter -U to specify username different than OS username we currently are using.

Also all this parameters can be taken from shell variables which names are self descriptive - if we set all of them we can just use plain psql command to connect:

PGHOST
PGPORT
PGDATABASE
PGUSER
PGPASSWORD

Check connected database

postgres=## select current_database();
 current_database
------------------
 postgres
(1 row)

Check current user

postgres=## select current_user;
 current_user
--------------
 postgres
(1 row)

Check IP and port used for connection

postgres=## select inet_server_addr(), inet_server_port();
 inet_server_addr | inet_server_port
------------------+------------------
 10.128.0.2       |             5432
(1 row)

Check PostgreSQL version

postgres=## select version();
                                                version                                                 
--------------------------------------------------------------------------------------------------------
 PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit
(1 row)

Check connection info

postgres=## \conninfo
You are connected to database "postgres" as user "postgres" on host "10.128.0.2" at port "5432".

Executing commands from shell

Execute single command from shell

[postgres@postgres-lab ~]$ psql -c "select current_time"
    current_time    
--------------------
 14:09:19.854598+00
(1 row)

Exacute sql script from shell

[postgres@postgres-lab ~]$ psql -f create_user.sql
CREATE ROLE
CREATE ROLE
CREATE ROLE

Combine single command with sql script from shell

[postgres@postgres-lab ~]$ psql -c "select current_time" -f create_user.sql -c "select current_time"
    current_time    
--------------------
 14:14:26.922453+00
(1 row)

CREATE ROLE
CREATE ROLE
CREATE ROLE
    current_time    
--------------------
 14:14:26.926545+00
(1 row)

psql metacommands

Check all available metacommands

Do it yourself to see all available commands - output trimmed to important ones!

postgres=## \?
General
  \copyright             show PostgreSQL usage and distribution terms
  \crosstabview [COLUMNS] execute query and display results in crosstab
  \errverbose            show most recent error message at maximum verbosity
  \g [FILE] or ;         execute query (and send results to file or |pipe)
  \gdesc                 describe result of query, without executing it
  \gexec                 execute query, then execute each value in its result
  \gset [PREFIX]         execute query and store results in psql variables
  \gx [FILE]             as \g, but forces expanded output mode
  \q                     quit psql
  \watch [SEC]           execute query every SEC seconds

  Query Buffer
    \e [FILE] [LINE]       edit the query buffer (or file) with external editor
    \ef [FUNCNAME [LINE]]  edit function definition with external editor
    \ev [VIEWNAME [LINE]]  edit view definition with external editor
    \p                     show the contents of the query buffer
    \r                     reset (clear) the query buffer
    \s [FILE]              display history or save it to file
    \w FILE                write query buffer to file

List objects in psql

• \d[S+] - list tables, views, and sequences
• \d[S+] NAME - describe table, view, sequence, or index
• \da[S] [PATTERN] - list aggregates
• \dA[+] [PATTERN] - list access methods
• \db[+] [PATTERN] - list tablespaces
• \dc[S+] [PATTERN] - list conversions
• \dC[+] [PATTERN] - list casts
• \dd[S] [PATTERN] - show object descriptions not displayed elsewhere
• \dD[S+] [PATTERN] - list domains
• \ddp [PATTERN] - list default privileges
• \dE[S+] [PATTERN] - list foreign tables
• \det[+] [PATTERN] - list foreign tables
• \des[+] [PATTERN] - list foreign servers
• \deu[+] [PATTERN] - list user mappings
• \dew[+] [PATTERN] - list foreign-data wrappers
• \df[anptw][S+] [PATRN]- list [only agg/normal/procedures/trigger/window] functions
• \dF[+] [PATTERN] - list text search configurations
• \dFd[+] [PATTERN] - list text search dictionaries
• \dFp[+] [PATTERN] - list text search parsers
• \dFt[+] [PATTERN] - list text search templates
• \dg[S+] [PATTERN] - list roles
• \di[S+] [PATTERN] - list indexes
• \dl - list large objects, same as \lo_list
• \dL[S+] [PATTERN] - list procedural languages
• \dm[S+] [PATTERN] - list materialized views
• \dn[S+] [PATTERN] - list schemas
• \do[S] [PATTERN] - list operators
• \dO[S+] [PATTERN] - list collations
• \dp [PATTERN] - list table, view, and sequence access privileges
• \dP[itn+] [PATTERN] - list [only index/table] partitioned relations [n=nested]
• \drds [PATRN1 [PATRN2]] - list per-database role settings
• \dRp[+] [PATTERN] - list replication publications
• \dRs[+] [PATTERN] - list replication subscriptions
• \ds[S+] [PATTERN] - list sequences
• \dt[S+] [PATTERN] - list tables
• \dT[S+] [PATTERN] - list data types
• \du[S+] [PATTERN] - list roles

Check syntax for SQL statement

postgres-## \h SELECT
Command:     SELECT
Description: retrieve rows from a table or view
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY grouping_element [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [...]

postgres-## \h UPDATE
Command:     UPDATE
Description: update rows of a table
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

URL: https://www.postgresql.org/docs/12/sql-update.html

postgres-## \h ALTER SYSTEM
Command:     ALTER SYSTEM
Description: change a server configuration parameter
Syntax:
ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | DEFAULT }

ALTER SYSTEM RESET configuration_parameter
ALTER SYSTEM RESET ALL

URL: https://www.postgresql.org/docs/12/sql-altersystem.html

Change your password in secure way

Setting password_encryption instruct psql to send password enrypted from client to database server.

postgres=## SET password_encryption = 'scram-sha-256';
SET
postgres=## \password
Enter new password:
Enter it again:
postgres=##

Saving passwords in your OS user account with .pgpass

.pgpass file is used to keep passwords for easy of connection with PostgreSQL database servers.

By default client looks for .pgpass in your home directory.

This file should on Linux has got 0600 permissions.

We can change location of .pgpass by setting shell variable PGPASSFILE .

Sample .pgpass can look like - of course we can add multiple lines in it for diffrent connections:

vi .pgpass
postgres-lab:5432:*:postgres:postgres12345

Below .pgpass sets password postgres12345 , for user postgres to database postgres when connecting to host postgres-lab on port 5432 .

After saving .pgpass we can connect to our database without providing password:

[postgres@postgres-lab ~]$ psql -h postgres-lab -p 5432
psql (12.3)
Type "help" for help.

postgres=##

Service file

Service files allows us to save connection descriptions and call them by simple name.

Service file can be on:

/etc/pg_service.conf
~/.pg_service.conf

PostgreSQL client always look first for suer level service file, which overrides system level one.

Sample record in service file will look like:

vi .pg_service.conf

[prod_db]
host=postgres-lab
port=5432
dbname=postgres

After saving service file we can connect with psql like this(password will come from .pgpass ):

[postgres@postgres-lab ~]$ psql service=prod_db user=postgres
psql (12.3)
Type "help" for help.

postgres=##

Troubleshooting connection

pg_isready binary allows us to check status of PostgreSQL local or remote instance

[postgres@postgres-lab ~]$ /usr/pgsql-12/bin/pg_isready -h postgres-lab -p 5432
postgres-lab:5432 - accepting connections

You can also set in postgresql.conf parameters for logging connection debug info:

log_connections = on
log_disconnections = on

After setting it remember to reload PostgreSQL server.

Logfile by default is located in $PGDATA /log directory - in our example configuration that will be:

[postgres@postgres-lab ~]$ ls -lah /postgresql/data/log/
total 24K
drwx------.  2 postgres postgres  162 Jul  3 00:00 .
drwx------. 20 postgres postgres 4.0K Jul  3 00:00 ..
-rw-------.  1 postgres postgres  153 Jul  3 10:07 postgresql-Fri.log
-rw-------.  1 postgres postgres    0 Jun 29 00:00 postgresql-Mon.log
-rw-------.  1 postgres postgres  186 Jun 28 21:46 postgresql-Sun.log
-rw-------.  1 postgres postgres 4.7K Jul  2 17:00 postgresql-Thu.log
-rw-------.  1 postgres postgres   72 Jun 30 19:46 postgresql-Tue.log
-rw-------.  1 postgres postgres    0 Jul  1 00:00 postgresql-Wed.log