In the course VM PostgreSQL is installed from prebuilt package to the following directory:
student$ sudo ls -l /usr/lib/postgresql/11
total 8 drwxr-xr-x 2 root root 4096 янв 16 17:18 bin drwxr-xr-x 3 root root 4096 янв 16 17:18 lib
The files of the server are owned by the root OS user.
In Ubuntu, access to the server utilities is provided by wrapper scripts. For example, the server is managed by pg_ctlcluster instead of pg_ctl.
During package installation, database cluster is automatically initialized and OS is configured to start database server on startup. Hence usually you don't need to start PostgreSQL manually.
To explicitly manage the server, use the following commands:
Here, alpha is the name of the database cluster.
Database cluster data is located in the /var/lib/postgresql/11/alpha directory.
This directory is known as PGDATA, after the name of the environment variable which, if set, is used by the server utilities as default value.
This directory is owned by the postgres OS user. Here is the contents of the directory:
postgres$ ls -l /var/lib/postgresql/11/alpha
total 84 drwx------ 16 postgres postgres 4096 фев 27 15:37 base drwx------ 2 postgres postgres 4096 фев 27 15:37 global drwx------ 2 postgres postgres 4096 янв 16 17:19 pg_commit_ts drwx------ 2 postgres postgres 4096 янв 16 17:19 pg_dynshmem drwx------ 4 postgres postgres 4096 фев 27 15:37 pg_logical drwx------ 4 postgres postgres 4096 янв 16 17:19 pg_multixact drwx------ 2 postgres postgres 4096 фев 27 15:19 pg_notify drwx------ 2 postgres postgres 4096 фев 27 15:19 pg_replslot drwx------ 2 postgres postgres 4096 янв 16 17:19 pg_serial drwx------ 2 postgres postgres 4096 янв 16 17:19 pg_snapshots drwx------ 2 postgres postgres 4096 фев 27 15:19 pg_stat drwx------ 2 postgres postgres 4096 янв 16 17:19 pg_stat_tmp drwx------ 2 postgres postgres 4096 янв 16 17:19 pg_subtrans drwx------ 2 postgres postgres 4096 фев 27 15:19 pg_tblspc drwx------ 2 postgres postgres 4096 янв 16 17:19 pg_twophase -rw------- 1 postgres postgres 3 янв 16 17:19 PG_VERSION drwx------ 3 postgres postgres 4096 фев 27 15:19 pg_wal drwx------ 2 postgres postgres 4096 янв 16 17:19 pg_xact -rw------- 1 postgres postgres 88 фев 27 15:19 postgresql.auto.conf -rw------- 1 postgres postgres 132 фев 27 15:19 postmaster.opts -rw------- 1 postgres postgres 109 фев 27 15:19 postmaster.pid
The main configuration file (postgresql.conf) is located in the following directory:
postgres$ ls -l /etc/postgresql/11/alpha
total 52 drwxr-xr-x 2 postgres postgres 4096 янв 16 17:19 conf.d -rw-r--r-- 1 postgres postgres 315 янв 16 17:19 environment -rw-r--r-- 1 postgres postgres 143 янв 16 17:19 pg_ctl.conf -rw-r----- 1 postgres postgres 4690 фев 27 15:19 pg_hba.conf -rw-r----- 1 postgres postgres 1636 янв 16 17:19 pg_ident.conf -rw-r--r-- 1 postgres postgres 23968 фев 27 15:37 postgresql.conf -rw-r--r-- 1 postgres postgres 317 янв 16 17:19 start.conf
Other configuration files are located here too.
Server message log is in the file:
postgres$ ls -l /var/log/postgresql/postgresql-11-alpha.log
-rw-r----- 1 postgres adm 8671 фев 27 15:37 /var/log/postgresql/postgresql-11-alpha.log
Let's look at the trailing lines of the message log:
postgres$ tail -n 10 /var/log/postgresql/postgresql-11-alpha.log
2019-02-27 15:19:58.276 MSK [8847] LOG: aborting any active transactions 2019-02-27 15:19:58.282 MSK [8847] LOG: background worker "logical replication launcher" (PID 8854) exited with exit code 1 2019-02-27 15:19:58.282 MSK [8849] LOG: shutting down 2019-02-27 15:19:58.309 MSK [8847] LOG: database system is shut down 2019-02-27 15:19:58.477 MSK [9258] LOG: listening on IPv4 address "127.0.0.1", port 5432 2019-02-27 15:19:58.478 MSK [9258] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2019-02-27 15:19:58.498 MSK [9259] LOG: database system was shut down at 2019-02-27 15:19:58 MSK 2019-02-27 15:19:58.505 MSK [9258] LOG: database system is ready to accept connections 2019-02-27 15:19:59.064 MSK [9266] [unknown]@[unknown] LOG: incomplete startup packet 2019-02-27 15:37:44.272 MSK [9258] LOG: received SIGHUP, reloading configuration files
Let's check the work_mem parameter value:
α=> SHOW work_mem;
work_mem ---------- 4MB (1 row)
The work_mem parameter specifies the amount of operating memory which is allocated to perform internal operationsi, such as sorting or hashing. If this amount is exceeded, temporary files on disk are used.
4MB is the default value and it's obviously too small. Let's increase it to, say, 16MB. For that, we can change postgresql.conf file and signal the server to reload configuration.
You can modify postgresql.conf file with any text editor. In this demonstration we use 'sed' editor from the command line. The file is owned by postgres, so the editor has to be started on behalf of that OS user.
postgres$ sed '/^work_mem/d' -i /etc/postgresql/11/alpha/postgresql.conf
postgres$ echo 'work_mem = 16MB' >> /etc/postgresql/11/alpha/postgresql.conf
Here we added a new line to the end of the file:
postgres$ tail -n 5 /etc/postgresql/11/alpha/postgresql.conf
# CUSTOMIZED OPTIONS #------------------------------------------------------------------------------ # Add settings for extensions here work_mem = 16MB
Now it's time to reload configuration. We'll use pg_ctlcluster utility for that. Again, it must be run on behalf of the postgres (or via the sudo command).
student$ sudo pg_ctlcluster 11 alpha reload
With 'reload' option PostgreSQL re-reads configuration files and updates parameters values accordingly. This is done without restarting the server.
Let's check the value of work_mem parameter:
α=> SHOW work_mem;
work_mem ---------- 16MB (1 row)
Changes took effect.
Most parameters can be set to a new value in the current session at runtime. For example, a query which sorts big volume of data can benefit from increased work_mem value:
α=> SET work_mem = '64MB';
SET
α=> SHOW work_mem;
work_mem ---------- 64MB (1 row)
New value lasts only until the end of the current session (or current transaction, in case of SET LOCAL).
Let's check our current connection:
α=> \conninfo
You are connected to database "tools_overview" as user "student" via socket in "/var/run/postgresql" at port "5432".
\c[onnect] command closes the connection and reopens a new one, without quitting psql.
With psql it is possible to output query results in different formats: aligned, unaligned, extended.
Aligned format is used by default:
α=> SELECT name, setting, unit FROM pg_settings LIMIT 7;
name | setting | unit
-------------------------+------------+------
allow_system_table_mods | off |
application_name | psql |
archive_command | (disabled) |
archive_mode | off |
archive_timeout | 0 | s
array_nulls | on |
authentication_timeout | 60 | s
(7 rows)
Columns are adjusted to width of values. Header and total number of returned rows are shown too.
The following are psql commands to change the output mode:
Let's disable alignment and header/totals:
α=> \a
Output format is unaligned.
α=> \t
Tuples only is on.
α=> SELECT name, setting, unit FROM pg_settings LIMIT 7;
allow_system_table_mods|off| application_name|psql| archive_command|(disabled)| archive_mode|off| archive_timeout|0|s array_nulls|on| authentication_timeout|60|s
α=> \t
Tuples only is off.
α=> \a
Output format is aligned.
Extended format is useful for a wide couple-of-rows query:
α=> SELECT name, setting, unit, category, context, source, sourcefile, sourceline, boot_val, reset_val, pending_restart FROM pg_settings WHERE name = 'work_mem' \gx
-[ RECORD 1 ]---+------------------------ name | work_mem setting | 65536 unit | kB category | Resource Usage / Memory context | user source | session sourcefile | sourceline | boot_val | 4096 reset_val | 16384 pending_restart | f
More formatting options are available with \pset command.
It is possible to run OS commands right in psql:
α=> \! ls | head -n 10
Desktop doc Documents Downloads get_handouts.sh israel2019 Music Pictures Public Templates
α=> \! pwd
/home/student
You can write output of a command to a file with \o[ut]:
α=> \a \t
Output format is unaligned. Tuples only is on.
α=> \pset fieldsep ''
Field separator is "".
α=> \o psql.log
α=> SELECT format($$SELECT '%I: ', count(*) FROM %I;$$, tablename, tablename) FROM pg_tables LIMIT 3;
Note that nothing is shown on the screen. Switch back to screen now:
α=> \o
Here is what we have got in the file:
α=> \! cat psql.log
SELECT 'pg_statistic: ', count(*) FROM pg_statistic; SELECT 'pg_type: ', count(*) FROM pg_type; SELECT 'pg_foreign_table: ', count(*) FROM pg_foreign_table;
This is valid SQL commands, so we can execute them with \i[nclude]:
α=> \i psql.log
pg_statistic: 398 pg_type: 380 pg_foreign_table: 0
Starting from PostgreSQL 11, you can run generated commands in a more convenient way with \gexec:
α=> SELECT format($$SELECT '%I: ', count(*) FROM %I;$$, tablename, tablename) FROM pg_tables LIMIT 3 \gexec
pg_statistic: 398 pg_type: 380 pg_foreign_table: 0
Back to the default formatting.
α=> \t \a
Tuples only is off. Output format is aligned.
Other ways to execute SQL commands from a file:
Just like environment variables in the shell, psql offers it's own variables.
Set a value:
α=> \set TEST Hi!
To get the value, use colon before variable name:
α=> \echo :TEST
Hi!
To unset the value:
α=> \unset TEST
α=> \echo :TEST
:TEST
For example, psql variables can be used to store frequently used queries. The following is a query to get top-5 biggest tables:
α=> \set top5 'SELECT tablename, pg_total_relation_size(schemaname||''.''||tablename) as bytes FROM pg_tables ORDER BY bytes DESC LIMIT 5;'
To run the query, simply type:
α=> :top5
tablename | bytes ----------------+--------- pg_depend | 1048576 pg_proc | 999424 pg_rewrite | 630784 pg_attribute | 606208 pg_description | 540672 (5 rows)
It is convenient to set the value of top5 variable in so-called resource file (.psqlrc), which is located in the user's home directory. Commands from .psqlrc are run automatically each time psql starts.
Query result can be written to a variable using \gset:
α=> SELECT current_setting('work_mem') AS current_work_mem \gset
α=> \echo The value of work_mem is: :current_work_mem
The value of work_mem is: 64MB
\set command without parameters shows all variables, including the predefined ones:
α=> \set
AUTOCOMMIT = 'on' COMP_KEYWORD_CASE = 'preserve-upper' DBNAME = 'tools_overview' ECHO = 'none' ECHO_HIDDEN = 'off' ENCODING = 'UTF8' ERROR = 'false' FETCH_COUNT = '0' HISTCONTROL = 'none' HISTFILE = 'hist' HISTSIZE = '500' HOST = '/var/run/postgresql' IGNOREEOF = '0' LASTOID = '0' LAST_ERROR_MESSAGE = '' LAST_ERROR_SQLSTATE = '00000' ON_ERROR_ROLLBACK = 'off' ON_ERROR_STOP = 'off' PORT = '5432' PROMPT1 = '%/%R%# ' PROMPT2 = '%/%R%# ' PROMPT3 = '>> ' QUIET = 'off' ROW_COUNT = '1' SERVER_VERSION_NAME = '11.1 (Ubuntu 11.1-1.pgdg16.04+1)' SERVER_VERSION_NUM = '110001' SHOW_CONTEXT = 'errors' SINGLELINE = 'off' SINGLESTEP = 'off' SQLSTATE = '00000' USER = 'student' VERBOSITY = 'default' VERSION = 'PostgreSQL 11.1 (Ubuntu 11.1-1.pgdg16.04+1) on i686-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 32-bit' VERSION_NAME = '11.1 (Ubuntu 11.1-1.pgdg16.04+1)' VERSION_NUM = '110001' current_work_mem = '64MB' top5 = 'SELECT tablename, pg_total_relation_size(schemaname||'.'||tablename) as bytes FROM pg_tables ORDER BY bytes DESC LIMIT 5;'
You can get help on predefined variables: \? variables
The end of demonstration.