Part 6. Single-byte encodings

The commands given below in this section do not need to be executed, but can be viewed:

1) postgres=# select collname from pg_collation where collname like '%ru%RU%';

collname

----------------

ru_RU

ru_RU.cp1251

 ru_RU.iso88595

ru_RU.utf8

ru_RU

ru_RU

ru-RU-x-icu

(7 rows)

2) Creating a database with a different collation type:

postgres=# create database lab01iso88595 LOCALE_PROVIDER =libc LC_COLLATE = ' ru_RU.iso88595 ';

ERROR: encoding "UTF8" does not match locale "ru_RU.iso88595"

DETAIL: The chosen LC_COLLATE setting requires encoding "ISO_8859_5".

The error indicates that the sorting is related to the encoding.

When creating a database, you can use any locale provider ( builtin , libc , icu ), not just the one used when creating the cluster.

3) Specify the encoding and locale provider:

postgres=# create database lab01iso88595 LOCALE_PROVIDER=libc LC_COLLATE = 'ru_RU.iso88595' ENCODING='ISO_8859_5';

ERROR: encoding "ISO_8859_5" does not match locale "en_US.UTF-8"

DETAIL: The chosen LC_CTYPE setting requires encoding "UTF8".

 The error indicates that ctype is also related to encoding.

4) Let's try more :

postgres=# create database lab01iso88595 LOCALE_PROVIDER=libc LC_COLLATE = 'ru_RU.iso88595' LC_CTYPE='ru_RU.iso88595';

ERROR: encoding "UTF8" does not match locale "ru_RU.iso88595"

DETAIL: The chosen LC_CTYPE setting requires encoding "ISO_8859_5".

The selected ctype requires specifying the encoding for the database being created.

5) Let us indicate All four parameter :

postgres=# create database lab01iso88595 LOCALE_PROVIDER=libc LC_COLLATE = 'ru_RU.iso88595' LC_CTYPE='ru_RU.iso88595' ENCODING='ISO_8859_5';

ERROR: new encoding (ISO_8859_5) is incompatible with the encoding of the template database (UTF8)

HINT: Use the same encoding as in the template database, or use template0 as template.

The error indicates that the template1 database cannot be used, the only template that can be used is template0 .

6) Let us indicate Name template :

postgres=# create database lab01iso88595 LOCALE_PROVIDER=libc LC_COLLATE = 'ru_RU.iso88595' LC_CTYPE='ru_RU.iso88595' ENCODING='ISO_8859_5' TEMPLATE= template0;

CREATE DATABASE

When creating a database with a non-default encoding for the cluster, it was necessary to specify all four parameters and the provider if it was different from the cluster provider.

7) Let's connect to the new database and check that the collation works correctly with single-byte encoding. We'll set it explicitly , but it's not necessary, since the collation value is used by default for this database:

postgres=# \c lab01iso88595 \\

SELECT n FROM unnest(ARRAY[' a ', ' e ', ' e ', ' F ', ' i ', ' E ', ' E ']) n ORDER BY n COLLATE " ru_RU.iso88595 " ;

\c postgres \\

drop database lab01iso88595;

You are now connected to database "lab01iso88595" as user "postgres".

 n

---

A

e

E

yo

Yo

AND

I

(7 rows)

You are now connected to database "postgres" as user "postgres".

DROP DATABASE

Works correctly, just like with UTF-8 encoding.

8) The builtin localization provider , although it works faster and does not require REFRESH COLLATION and rebuilding indexes when updating the operating system localization libraries, but it incorrectly sorts the letters ё , Ё in all three of its locales ( C , C . UTF -8 , PG _ UNICODE _ FAST ):

postgres=# create database lab01builtin LOCALE_PROVIDER=builtin BUILTIN_LOCALE='PG_UNICODE_FAST' TEMPLATE=template0;

\c lab01builtin \\

SELECT n FROM unnest(ARRAY[' a ', ' e ', ' yo ', ' Zh ', ' i ', ' yo ',' e ']) n ORDER BY n;

\c postgres \\

drop database lab01builtin;

CREATE DATABASE
You are now connected to database "lab01builtin" as user "postgres".
n
---
Yo 
E
AND
A
e
I
yo
(7 rows)

You are now connected to database "postgres" as user "postgres".

DROP DATABASE

Part 7. Using Management Utilities

Let's take a look at command-line utilities that act as wrappers for SQL commands. You might find them convenient to use.

1) Review the parameters of the database creation utility. Linux command-line utilities typically have a parameter (switch) named --help or -h with a brief description of the parameters. Exit psql and run the command as the postgres user :

postgres=# \q

postgres @tantor:~$ createdb --help

There are strategies for creating a database: wal_log and file_copy.

Create a database named lab01database :

postgres@tantor:~$ createdb lab01database

No error was issued, which means the database was created.

2) View the list of cluster databases and their default tablespaces using the oid2name utility. Verify that the lab01database database is listed:

postgres@tantor:~$ oid2name

All databases:

Oid Database Name Tablespace

----------------------------------

16552 lab01database pg_default

5 postgres pg_default

4 template0 pg_default

1 template1 pg_default

3) Create a user named lab01user , with the same password and with attributes that allow connecting to the cluster databases, and the superuser attribute:

postgres@tantor:~$ createuser lab01user --login --superuser -P

Enter password for new role: lab01user

Enter it again: lab01user

postgres@tantor:~$ 

When you enter a password, it is not printed on the screen.

4) Run the cluster data export utility in global object export mode. Global objects are shared objects across all databases in the cluster. By default, the utility outputs generated commands to stdout (the terminal screen).

postgres@tantor:~$ pg_dumpall -g

--

-- PostgreSQL database cluster dump

--

SET default_transaction_read_only = off;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = on;

--

-- Roles

--

CREATE ROLE lab01user;

ALTER ROLE lab01user WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:..';

CREATE ROLE postgres;

ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS; 

The commands issued will include a command to recreate the user that was just created.

5) Vacuum all databases and freeze rows:

postgres@tantor:~$ vacuumdb -a -F

vacuumdb: vacuuming database "lab01database"

vacuumdb: vacuuming database "postgres"

vacuumdb: vacuuming database "template1" 

6) Verify that the cluster is running and accepting connections 

postgres@tantor:~$ pg_isready

/var/run/postgresql:5432 - accepting connections

Part 8. Configuring the psql terminal client

1) Verify that you are in the postgres user's terminal by looking at the command line terminal prompt:

postgres @tantor:~$

2) Launch psql and exit the utility's interactive mode. To exit, you can use the \q command, the <Ctrl+D> key combination , quit , or exit . 

postgres@tantor:~$ psql -q

postgres=# \q

postgres@tantor:~$ 

psql and terminal prompts are different. This is useful to avoid entering SQL commands in the operating system terminal, and vice versa.

3) Look at the contents of the .psqlrc file : 

postgres @ tantor :~$ cat ~/. psqlrc

\setenv PAGER ' less -XS'

\setenv PSQL_EDITOR '/usr/bin/ mcedit '

\pset pager off

postgres@tantor:~$

When editing procedures, functions, and views in psql , the mcedit editor will be used . The less utility will be used for paginated output . However, paginated output is disabled because it is inconvenient.

To add lines to the end of a file, you can use the command:

echo "\set ON_ERROR_ROLLBACK interactive" > > ~/.psqlrc

Setting ON _ ERROR _ ROLLBACK The interactive option is convenient because when working in interactive mode, psql implicitly creates a savepoint before each command. If the command fails, the savepoint will be rolled back and the transaction will not fail. Savepoints slow down the session, so this option should only be set for interactive mode.

4) Graphical editors can be used. AstraLinux comes with the kate graphical editor installed by default . However, if you use the su utility to switch the terminal to a different operating system user, the graphical editor will not start. In this case, you can use the commands below instead of su . The commands in this section are for reference only and do not need to be executed.

 postgres@tantor:~$ exit

logout

root@tantor:/home/astra# exit

exit

astra@tantor :~$ ssh -X postgres@localhost

The authenticity of host 'localhost (::1)' can't be established.

EC25519 key fingerprint is SHA256:12VsUcC5hw5I1zr015AJ8C+xsN0m5h+IlU2M/xdNg6o.

Are you sure you want to continue connecting (yes/no/[fingerprint])? yes

Warning: Permanently added 'localhost' ( EC25519 ) to the list of known hosts.

postgres@localhost's password: postgres

/usr/bin/xauth: file /var/lib/postgresql/.Xauthority does not exist

postgres@tantor:~$ export PSQL_EDITOR=kate

postgres@tantor:~$ pg_ctl stop

postgres@tantor:~$ sudo systemctl start tantor-se-server-18

postgres@tantor:~$ kate

postgres@tantor:~$ exit

Can be removed from ~/.psqlrc line \setenv PSQL_EDITOR /usr/bin/mcedit

SSH , do not start the instance using the pg_ctl start utility , as the instance will stop after the SSH connection is closed . This is because the parent process that started the postgres process is stopped because, by default, KillUserProcesses = yes , but this value can be changed in /etc/systemd/logind.conf . When connected via SSH , start the instance with the command sudo systemctl start tantor-se-server-18. 

5) Run psql and enable pagination :

postgres@tantor:~$ psql -q

postgres=# \pset pager on 

psql command help by typing the command \? and scroll down to the Query subsection by pressing the <Enter> key on your keyboard Buffer :

postgres=# \?

...

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

You can use the keys z to scroll up, b to scroll down, and q to quit. You can also scroll the terminal buffer with <Shift+PgUp> <Shift+PgDn>.

7) If you prefer to read the tooltip in Russian, set the LC_MESSAGES environment variable to set the language for utility messages. This can be done at the terminal level; the setting will remain in effect until you close the terminal.

Press <Ctrl+D> on your keyboard (or type \q and then <Enter>). <Ctrl+D> is convenient because it's universal and faster to type.

Dial command :

postgres@tantor:~$ export LC_MESSAGES=ru_RU.utf8

unset language

8) This step is optional. If you want Russian-language messages to be permanent, enter the following commands:

postgres@tantor:~$ cp .bash_profile .bash_profile.OLD

echo "export LC_MESSAGES=ru_RU. utf8 " > > ~/.bash_profile

echo " unset LANGUAGE " > > ~ / .bash_profile

9) If you type one ">" instead of two " > > " symbols , the file contents will be erased. The double symbol adds a line to the end of the file. There may be a file in the home directory .profile.  This file is inconvenient because if there is a file in the home directory .bash_profile or .bash_login , then the .profile file does not work.

Run psql and repeat the \? command . The command history is saved, and you can select commands from the history by typing the up or down arrow keys on your keyboard, and press <Enter> to repeat them.

postgres@tantor:~$ psql -q

postgres=# \?

Request buffer

\e [FILE] [LINE] edit the query buffer (or file) in an external editor

\ef [ FUNCTION [LINE]] edit function definition in external editor

\ev [ VIEW NAME [LINE]] edit view definition in external editor

\p print the contents of the query buffer

\r clear the request buffer

\s [FILE] display history or save it to a file

\w FILE write the request buffer to a file

: q 

If you want to stop displaying the hint, press the " q " key. 

10) Read the highlighted text. The \p \r commands are often forgotten or unknown, but they are useful.

How does psql interact with the editor program? When you type the commands \e \ef \ev , the editor is launched, and psql passes it the text you want to edit and the path to a temporary file, which you usually don't see. In the example below, the file name is displayed on the first line of the image as /tmp/psql/edit.6652.sql

Next, you edit the text using the editor's tools and click "Save" and "Close." The editor saves the text to a file, and psql receives a notification that the editor is closed. Behind the scenes, psql opens the file and loads it into the buffer, just as if you had typed the file's contents.

Note: If you don't include a semicolon or a newline at the end of a command you've typed or are editing while in the editor, or if you don't include one while already in psql , the command won't be executed, and you'll continue filling the buffer. This can make it difficult to use the \e \ef commands. And \ev , encouraging the use of graphical tools such as pgAdmin.

11) Call the view creation editor with the command \ev Type the command as shown below, press F2 (save) and F10 (exit). You can choose your preferred editor if desired. In the kate editor, which can be used in AstraLinux, the keyboard shortcuts are: <Ctrl+S> to save, <Ctrl+Q> to exit.

postgres=# \ev

CREATE VIEW

12) Team \p Look at the last command. The command was obtained by psql from the editor.

After sending commands for execution: 

postgres=# \p

CREATE VIEW lab01view AS

SELECT now();

13) You can also look at the definition of a view or subroutine (routines, which include procedures and functions):

\sf[+] FUNCTION_NAME show function definition

\sv[+] PRESENT_NAME show view definition

Type :

\sv l<TAB><ENTER>

Where <TAB> is the tab key, <ENTER> is also a key on the keyboard.

After pressing the <TAB> key, psql will complete the view name. If there are many views starting with "l" (or none at all), it will not complete the view name. In this case, pressing the <TAB> key a second time will display a list of candidates. You can type a few more characters, press <TAB> again, and then execute what you've typed by pressing <ENTER>.

postgres=# \sv lab01view

CREATE OR REPLACE VIEW public.lab01view AS

SELECT now() AS now

postgres=#

Note that there is no semicolon at the end of the command. It also won't appear when you open the editor. After " now ", you need to insert ; and a carriage return.

psql's most obscure functionality &mdash;interacting with the editor. The rest of the information is much simpler.

Part 9. Using the psql terminal client

1) Run the commands:

postgres=# begin transaction;

BEGIN

2) We started transaction . Note that the prompt has changed&mdash;an asterisk has appeared. In psql , with the default prompt, you can see whether there is an open transaction so you can decide whether to commit it.

Next, we type a command in several lines.

Type SELECT :

postgres = * # select

3) Note that the prompt has changed again: a dash has appeared instead of the equals symbol. Continue typing the command and end it with a semicolon:

postgres - * # tantor_version();

        tantor_version

-------------------------------

Tantor Special Edition 18.3.0

(1 row)

4) Note that the prompt has changed again: the dash has been replaced by the equals symbol. This means there is no unfinished command in the buffer, and you will be typing the first line of the command.

Type the erroneous command and send it for execution with a semicolon: 

postgres= * # ffff;

ERROR: syntax error at or near "ffff"

LINE 1: ffff;

A syntax error occurred. Note that the asterisk, which denotes an open transaction, has been replaced by an exclamation point. This means the transaction is still open, but it has entered a failed state, and in this state, a transaction cannot commit, only rollback. Transactions enter a failed state infrequently, only after certain errors considered so serious that the accumulated statements in the transaction cannot be committed. For example, access serialization errors. What's dangerous about the "ffff" command ? The server process receives it and sees that it's something completely out of the ordinary; a programmer couldn't have written such a command. The server process expects the commands to come from an application written and tested by the programmer. Therefore, it believes the transaction needs to be put into a failed state.

5) Let's check that if we send the correct command for execution, type :

postgres=!# select 1;

ERROR: current transaction is aborted, commands ignored until end of transaction block

An error is thrown that the command failed, and any commands will be ignored by the server process until the client "voluntarily" completes the transaction.

6) We complete the transaction using one of the two transaction completion commands:

postgres= ! # commit;

ROLLBACK

Please note that a transaction placed in a failed state cannot commit; it can only be rolled back completely or to a savepoint, if one was set. The server process returns "transaction completed by rolling back" in response to the COMMIT command.

There's an ON_ERROR_ROLLBACK parameter that prevents the loss of the results of executed commands. This parameter forces psql to set a savepoint after each command, which is undesirable because it increases the transaction counter (xid) usage. If you set it, it's best to set it to INTERACTIVE , so savepoints will be set when you're working interactively in psql .

7) Install this parameter :

postgres=# \set ON_ERROR_ROLLBACK INTERACTIVE 

8) Repeat the commands from the previous example:

postgres=# begin;

BEGIN

postgres=*# select 1;

?column?

----------

1

(1 line )

postgres=*# ffff;

ERROR: syntax error at or near "ffff"

LINE 1: ffff;

^

postgres=*# commit ;

COMMIT

The transaction was closed by commit, not rollback.

9) Let's look at how psql processes its commands&mdash;what it sends to the server process to produce a nice result. Let's look at the roles in the cluster. In English, this would be "describe user," abbreviated from the first letters of the word "du." Let's add a backslash, the common preface for all psql commands . Without the backslash, it's an SQL command and is sent to the server process for execution as text. To send a command for execution, use a semicolon ";" &mdash;otherwise, how will psql know you've finished typing the command?

Type:

postgres=# \du

List of roles

Role Name | Attributes

-----------+---------------------------------------

lab01user | Superuser, Creates roles, Creates database

postgres | Superuser, Create Roles, Create Databases, Replication, Skip RLS

10) Set the psql parameter, which will show which command psql itself generates and sends for execution:

postgres=# \set ECHO_HIDDEN on

11) Repeat command :

postgres=# \du

********* REQUEST *********

SELECT r.rolname, r.rolsuper, r.rolinherit,

r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,

r.rolconnlimit, r.rolvaliduntil

, r.rolreplication

, r.rolbypassrls

FROM pg_catalog.pg_roles r

WHERE r.rolname !~ '^pg_'

ORDER BY 1;

****************************

 List of roles

Role Name | Attributes

-----------+----------------------------------------

lab01user | Superuser, Creates roles, Creates database

postgres | Superuser, Create Roles, Create Databases, Replication, Skip RLS

12) Copy and paste the command text. You can use the keyboard shortcuts <Ctrl+Shift+c> <Ctrl+Shift+v> to do this.

 postgres =# SELECT r . rolname , r . rolsuper , r . rolinherit , r . rolcreaterole , r . rolcreatedb , r . rolcanlogin , r . rolconnlimit , r . rolvaliduntil , r . rolreplication , r . rolbypassrls

FROM pg_catalog.pg_roles r

WHERE r.rolname !~ '^pg_'

ORDER BY 1 \gx

-[ RECORD 1 ]--+----------

rolname | lab01user

rolsuper | t

rolinherit | t

rolcreaterole | t

rolcreatedb | t

rolcanlogin | t

rolconnlimit | -1

rolvaliduntil |

rolreplication | f

rolbypassrls | f

-[ RECORD 2 ]--+----------

rolname | postgres

rolsuper | t

rolinherit | t

rolcreaterole | t

rolcreatedb | t

rolcanlogin | t

rolconnlimit | -1

rolvaliduntil |

rolreplication | t

rolbypassrls | t

You can see what information psql receives and compare it with how it displays it: psql The INHERIT and LOGIN attributes weren't displayed . Why? Because these are the default values when creating a user with the CREATE USER command . Default values aren't displayed. Their inverse values will be displayed: "Not inherited, Login denied." This feature isn't intuitive, so we'll cover it in detail.

13) Use the \? command to view help for the \connect command (a shortened version of the \с command )

Compound:

\c[connect] {[ DB |- USER |- SERVER |- PORT |-] | conninfo}

connect to another database

(current: "postgres")

\conninfo information about the current connection

14) Try different connection combinations. The tab key allows you to end a parameter, since psql has access to the list of database and user names in the current connection. The purpose of this connection sequence is to remember the order of the \c command parameters: database user host port . If you want to keep a parameter the same, replace it with a dash. <TAB><ENTER> - tab and carriage return (new line) keys on the keyboard.

 postgres=# \c la <TAB><ENTER>

You are connected to the database "lab01database" as user "postgres".

lab01database=# \c - la <TAB><ENTER>

You are connected to the database "lab01database" as user "lab01user".

lab01database=# \c - - localhost

You are now connected to the database "lab01database" as user "lab01user" (server "localhost": address "127.0.0.1", port "5432").

lab01database=# \c - - - 5432

You are connected to the database "lab01database" as user "lab01user".

lab01database=# \c postgres p <TAB><ENTER>

You are connected to the database "postgres" as user "postgres".

15) Let's see how to get the sample result in the format of a web page and view it in a browser. Open a new terminal window ( astra operating system user ).

16) Run psql and make sure you run it in the astra user's terminal :

astra @ tantor : ~ $ psql -q​

 17) Set the HTML output format :

postgres =# \ pset format html

 18) Redirect the output to a file called file.html :

postgres=# \o file.html

 19) Issue any command whose result is difficult to read in the terminal (the command line does not fit the width of the terminal window):

postgres=# show all; 

20) Disable output to file:

postgres=# \o

21) Launch a browser window while exiting psql :

postgres=# \! xdg-open file.html

22) Wait for the browser window to open. Close psql :

postgres=# \q 

23) Close the terminal window:

astra@tantor :~$ <CTRL+d>

24) Close the browser window and return to the psql window . Let's see what other output formats are available. Type V psql :

postgres=# \pset format aaa

\pset: allowed formats are aligned, asciidoc, csv, html, latex, latex-longtable, troff-ms, unaligned, wrapped

25) Select the aligned format , it is used by default:

postgres=# \pset format aligned

Output format is aligned.

26) Run the command:

postgres=# show all;

zzbq on your keyboard. and see the effect.

z - next page, b - previous, q - finish output and return prompt.

27) Complete command :

postgres=# \pset format wrapped

Output format is wrapped.

28) Complete command :

postgres=# show all;

Press the zzb h keys on your keyboard . Read the description of the available keys. Practice scrolling through the results.

29) Compare the differences. Perhaps the wrapped format (word wrapping) will be more convenient than aligned .

30) Let's check how to execute operating system commands without exiting psql . The Linux command "pwd" displays the current directory.

Run the "pwd" or "ls" command (lists files) without exiting psql:

postgres =# \! pwd

/ var / lib / postgresql

31) Set a color prompt that will display the server process number (pid) in gray:

\set PROMPT1 ' %[%033[0;90m%] [%p] %[%033[0m%] %[%033[0;31m%] %n %[%033[0m%] @ %[%033[0;34m%] %m %[%033[0m%] : %[%033[0;32m%] %> %[%033[0m%] %[%033[0;36m%] %~ %[%033[0m%] %[%033[0;33m%]%[%033[5m%] %x %[%033[0m%] %[%033[0m%] %R%# '

\set PROMPT2 ' %[%033[0;90m%] [%l] %[%033[0m%] %[%033[0;31m%] %n %[%033[0m%] @ %[%033[0;34m%] %m %[%033[0m%] : %[%033[0;32m%] %> %[%033[0m%] %[%033[0;36m%] %~ %[%033[0m%] %[%033[0;33m%]%[%033[5m%] %x %[%033[0m%] %[%033[0m%] %R%# '

* and ! symbols to attract attention.

Help on what the symbols mean if you want to create your own prompt:

%p server process number

%n role. (can be changed during a session using the SET SESSION AUTHORIZATION command; )

%m host name or [local] if the connection is made via a Unix socket

%> instance port number

%/ database name

%~ The name of the database. If this is the default database, ~ is displayed instead of the name.

%# for the superuser - the # symbol , for other roles - the >%l symbol , the line number in the input buffer.

%R for PROMPT1 displays = if the session is in an inactive branch of a conditional block @ in single-line input mode ^ if the session is disconnected from the database - !

for PROMPT2 if the command is not completed &ndash;

if there is an unclosed comment *

if there is an unclosed quotation mark, then '

If there is an unterminated double quote, then "

If there is a started but unfinished $line$ (usually when typing functions), then $ 

if there is a left parenthesis and the right parenthesis is not entered, then (

Symbols that it displays PROMPT2 are important because if you forget to type the closing apostrophe, pressing the key <ENTER> or ; or \r , there will be no reaction until you type an apostrophe:

If you need to display the role and base:

\set PROMPT1 '%[%033[0;31m%] %n %[%033[0m%] @ %[%033[0;36m%] %/ %[%033[0m%] %[%033[0;33m%]%[%033[5m%] %x %[%033[0m%] %[%033[0m%] %R%# '

\set PROMPT2 '%[%033[0;31m%] %n %[%033[0m%] @ %[%033[0;36m%] %/ %[%033[0m%] %[%033[0;33m%]%[%033[5m%] %x %[%033[0m%] %[%033[0m%] %R%# '

32) Look at how the query result is displayed:

postgres @ postgres =# select usename, usesysid, usesuper from pg_user;

usename | usesysid | usesuper

----------+----------+----------

postgres | 10 | t

(1 row)

33) Set the line drawing style to unicode characters :

postgres @ postgres =# \pset linestyle unicode

Line style is unicode .

Let's repeat the request (press the up arrow on the keyboard twice and then the <ENTER> key)

postgres @ postgres =# select usename, usesysid, usesuper from pg_user;

usename │ usesysid │ usesuper

──────────┼──────────┼──────────

postgres │ 10 │ t

(1 row)

34) Change the border display style :

postgres @ postgres =# \ pset border 0

Border style is 0.

35) Repeat your request:

postgres @ postgres =# select usename, usesysid, usesuper from pg_user;

usename usesysid usesuper

──────── ──────── ────────

Postgres 10

(1 row)

The display has become more compact. 

36) Change the border display style:

postgres @ postgres =# \pset border 2

Border style is 2.

postgres @ postgres =# select usename, usesysid, usesuper from pg_user;

┌──────────┬──────────┬───────────┐

│ usename │ usesysid │ usesuper │

├──────────┼──────────┼────────────┤

│ postgres │ 10 │ t │

└──────────┴───────────┴───────────┘

(1 row )

You can choose the most convenient style for displaying query results. To make it permanent, you can edit the ~/.psqlrc file and add the commands described above.

Part 10. Restoring a saved cluster

In step 4 of part 2, we saved the existing cluster before creating a new one. Let's restore the cluster. Make sure the terminal is open as the postgres user .

1) Stop the instance:

postgres @ tantor :~$ pg _ ctl stop

2) Execute commands :

postgres@tantor:~$ mkdir $PGDATA/../data.afterLAB1

mv $PGDATA/* $PGDATA/../data.afterLAB1

mv $PGDATA/../data.SAVE/* $PGDATA

3) Launch copy :

postgres@tantor:~$ sudo systemctl start tantor-se-server-18

4) Check performance instance :

postgres@tantor:~$ psql -qc "select datname from pg_database;"

datname

-----------

postgres

template1

template0

(3 rows)

Chapter 2a. Architecture

Part 1. Transaction in psql

1) Let's create table :

postgres=# create table a(id integer);

CREATE TABLE

4) Let's see what happened:

postgres=# \dt a

List of tables

Schema | Name | Type | Owner

--------+------+-------+----------

public | a | table | postgres

5) Open the transaction:

postgres=# begin ;

BEGIN

6) Insert the first line. Note that you can use tabs to add keywords and even complex constructions.

postgres= * # insert into a values (1);

INSERT 0 1

Note the appearance of an asterisk in the line - this means that a transaction is in progress.

7) Let's try to view the first row of the table in the second terminal. Open a second terminal and run psql in it :

astra @ tantor :~$ sudo su - postgres

postgres@tantor:~$ psql -q

postgres=#

9) Contact To table :

postgres=# select * from a;

 ID

----

(0 lines)

We don't see the first line yet. Only the committed data is visible. There is no "dirty read."

10) In the first terminal we will record the transaction.

postgres=*# commit ;

COMMIT

11) In the second terminal, let's look at the table again.

postgres=# select * from a;

ID

----

  1

(1 line)

The recorded changes became visible.

Part 2. List of background processes

1) Let's see where the PGDATA directory is located , where the DB cluster files are located.

postgres=# show data_directory;

data_directory

---------------------------------------

/var/lib/postgresql/tantor-se-18/data

(1 row)

2) Go out from psql :

postgres=# \q

3) To view the list of processes, use the ps utility :

postgres@tantor:~$ cat /var/lib/postgresql/tantor-se-18/data/postmaster.pid

12179

/var/lib/postgresql/tantor-se-18/data

1777122902

5432

/var/run/postgresql

localhost

1464138 55

ready

4) Let's take number process (pid, process id) postmaster, it V first line : 

postgres@tantor:~$ ps -o command --ppid 12179

COMMAND

postgres: logger

postgres: io worker 0

postgres: io worker 1

postgres: io worker 2

postgres: checkpointer

postgres: background writer

postgres: walwriter

postgres: autovacuum launcher

postgres: autoprewarm leader 
postgres: logical replication launcher

postgres : postgres postgres [ local ] idle

The colors indicate background processes, the rest are server processes servicing sessions.

The list of processes can also be seen through the pg_stat_activity view.

5) Run in terminal with psql:

postgres=# select pid, backend_type, backend_start FROM pg_stat_activity;

pid | backend_type | backend_start

-------+------------------------------+--------------------------------

12200 | client backend | 2026-04-25 16:16:16.836795+03

12188 | autovacuum launcher | 2026-04-25 16:15:02.106041+03

12190 | logical replication launcher | 2026-04-25 16:15:02.107414+03

12181 | io worker | 2026-04-25 16:15:02.092927+03

12182 | io worker | 2026-04-25 16:15:02.093814+03

12183 | io worker | 2026-04-25 16:15:02.094654+03

12184 | checkpointer | 2026-04-25 16:15:02.09533+03

12185 | background writer | 2026-04-25 16:15:02.095476+03

12187 | walwriter | 2026-04-25 16:15:02.104866+03

(8 lines )

Part 3. Buffer Cache, EXPLAIN Command

1) Add rows to table "a":

postgres=# insert into a select id from generate_series(1,10000) AS id;   

INSERT 0 10000

2) In the second terminal, reboot the server:

postgres@tantor:~$ sudo systemctl restart tantor-se-server-18

3) In the first terminal, reconnect:

postgres=# \c

You are now connected to database "postgres" as user "postgres".

You are connected to the database "postgres" as user "postgres".

4) Use the EXPLAIN command to see where the information comes from:

postgres=# explain (analyze) select * from a;

QUERY PLAN

-----------------------------------------

Seq Scan on a (cost=0.00..145.0 1 rows=10001 width=4) (actual time=0.035..1.952 rows=10001 .1 loops=1)

 Buffers: shared read=45

Planning:

Buffers: shared hit=16 read=6 dirtied=3

Planning Time: 0.428 ms

Execution Time: 2.948 ms

(6 lines)

Note the Buffers line . This information was retrieved from disk or the operating system's page cache . After Planning , this line reflects the blocks that were read to create the command execution plan.

5) Execute team more once :

postgres=# explain (analyze) select * from a;

QUERY PLAN

----------------------------------

Seq Scan on a (cost=0.00..145.00 rows=10000 width=4) (actual time=0.016..1.383 rows=10000 loops=1)

   Buffers: shared hit=45

Planning Time: 0.063 ms

 Execution Time: 2.355 ms

(4 lines)

The result changed. Now blocks were read from the buffer cache.

Part 4. Pre-Record Log. Where is it stored?

In the first terminal, run the command:

postgres@tantor:~$ ls -l /var/lib/postgresql/tantor-se-18/data/pg_wal

total 16392

-rw------- 1 postgres postgres 16777216 Apr 25 16:36 0000000100000000000000001

drwx------ 2 postgres postgres 4096 Apr 23 16:02 archive_status

drwx------ 2 postgres postgres 4096 Apr 23 16:02 summaries

Files magazine pre-recordings are located V directories pg_wal segments 16 megabytes each .

Part 5. Checkpoint

1) The checkpoint is performed periodically, let's look at the second terminal to see what interval is set.

postgres=# show checkpoint_timeout;

checkpoint_timeout

--------------------

5 min

(1 raw )

2) The checkpoint can be started manually.

postgres=# checkpoint ;

CHECKPOINT

Part 6. Disaster Recovery

1) Add new lines in the second terminal:

postgres=# insert into a select id from generate_series(1,10000) AS id;

INSERT 0 10000

2) Force stop the instance. Determine the PID of the postmaster process:

postgres@tantor:~$ cat /var/lib/postgresql/tantor-se-18/data/postmaster.pid

12563

/var/lib/postgresql/tantor-se-18/data

1713849023

5432

/var/run/postgresql

*

1048641 24

ready

postgres@tantor:~$ kill -QUIT 12563

3) Let's launch copy servers .

postgres@tantor:~$ sudo systemctl start tantor-se-server-18

Restoration is underway.

4) In the second window, let's see if the inserted lines have been saved.

postgres=# \c

You are connected to the database "postgres" as user "postgres".

postgres=# select count(*) from a;

count

-------

20001

(1 line )

5) Delete the table :

postgres=# drop table a;

DROP TABLE

postgres=# \dt

Tables No .

Chapter 2 b . Multiversioning

Part 1. Inserting, updating, and deleting a row

1) Run psql :

postgres @ tantor :~$ psql - q

postgres=#

2) Let's create arbitrary table :

postgres=# create table a(id integer);

CREATE TABLE

3) Let's see what happened:

postgres=# \dt a

List of tables

Schema | Name | Type | Owner

--------+------+-------+----------

public | a | table | postgres

4) Insert the first row into the table:

postgres=# insert into a values(100);

INSERT 0 1

5) Let's see what the transaction number xmin is:

postgres=# select xmin, xmax, * from a;

 xmin | xmax | id

------+------+-----

777 | 0 | 100

(1 line)

The result is 777 - this is the transaction number in which the first version of the row was created.

6) Let's start the transaction:

postgres=# begin ;

BEGIN

7) Update the first line.

postgres=*# update a set id = 200;

UPDATE 1

8) Let's look at the result in the same transaction:

postgres=*# select xmin, xmax, * from a;

 xmin | xmax | id

------+------+-----

779 | 0 | 200

(1 line)

9) Ensure that the transaction sees its changes.

What will happen if I access it in another session, will the id be equal to 100 or 200?
10) Run psql :

postgres@tantor:~$ psql -q

postgres=# select xmin, xmax, * from a;

 xmin | xmax | id

------+------+-----

777 | 779 | 100

(1 line)

Note that xmax has changed - this means that a second version of the row already exists, but it has not yet been committed.

11) In the first terminal we record the transaction:

postgres=*# commit ;

COMMIT

12) In the second terminal we now see the second line:

postgres=# select xmin, xmax, * from a;

 xmin | xmax | id

------+------+-----

779 | 0 | 200

(1 line)

13) Now let's see what deletion looks like. Let's open a transaction in the first terminal:

postgres=# begin;

BEGIN

14) Delete line :

postgres=*# delete from a;

DELETE 1

postgres=*# select xmin, xmax, * from a;

 xmin | xmax | id

------+------+----

(0 rows)

The first transaction does not see the row, the row is deleted, but the deletion is not yet committed.

15) In second terminal :

 postgres=# select xmin, xmax, * from a;

 xmin | xmax | id

------+------+-----

779 | 781 | 200

(1 line)

The line is still visible, but xmax has changed again.

16) In the first terminal we record the transaction:

postgres=*# commit;

COMMIT

17) In the second terminal we now see that the line has been deleted:

postgres=# select xmin, xmax, * from a;

xmin | xmax | id

------+------+----

(0 rows)

Part 2. Row Version Visibility at Different Transaction Isolation Levels

1) Open the first transaction and insert the line:

postgres=# begin;

BEGIN

2) Let's look at the insulation level:

postgres=*# show transaction_isolation;

transaction_isolation

-----------------------

read committed

(1 line )

postgres=*# insert into a values(100); 

INSERT 0 1

postgres=*# select xmin, xmax, * from a;

 xmin | xmax | id

------+------+-----

1571 | 0 | 100

(1 line)

3) Let's start the second transaction in the second terminal and refer to the table:

postgres=# begin;

BEGIN

postgres=*# select xmin, xmax, * from a;

xmin | xmax | id

------+------+----

(0 lines )

4) Let's see level isolation :
postgres=*# SHOW transaction_isolation;

transaction_isolation

-----------------------

read committed

(1 row)

5) While the new row isn't visible yet, let's commit the first transaction:

postgres=*# commit;

COMMIT

6) In the second window, we will again refer to the table. What shall we see ?

postgres=*# select xmin, xmax, * from a;

xmin | xmax | id

------+------+-----

1571 | 0 | 100

(1 line )

7) Let's fix it the second transaction :

postgres=*# commit;

COMMIT

The changes became visible. This is the anomaly of non-repeatable reading.

Now in the first window we will start a transaction at the repeatable read level.

8) Insert more one line :

postgres=# BEGIN ISOLATION LEVEL REPEATABLE READ;

BEGIN

postgres=*# insert into a values (200);

INSERT 0 1

postgres=*# select xmin, xmax, * from a;

 xmin | xmax | id

------+------+-----

1571 | 0 | 100

1572 | 0 | 200

(2 lines)

9) In the second transaction, we will access the table in a new transaction at the same level.

postgres=# BEGIN ISOLATION LEVEL REPEATABLE READ;

BEGIN

postgres=*# select xmin, xmax, * from a;

xmin | xmax | id

------+------+-----

1571 | 0 | 100

(1 line)

10) Now we commit the first transaction:

postgres=*# commit;

COMMIT

11) Let's look at the second transaction again:

postgres=*# select xmin, xmax, * from a;

xmin | xmax | id

-----+------+-----

1571 | 0 | 100

(1 line)

Changes are not visible. At this level, transaction operators work with only one snapshot of the data.

12) Let's commit the second transaction:

postgres=*# commit;

COMMIT

Part 3. Transaction state by CLOG

1) Let's open the first transaction and look at the state after insertion:

postgres=# begin;

BEGIN

postgres=*# INSERT INTO a VALUES(300);   

INSERT 0 1

postgres=*# Select xmin, xmax, * from a;

 xmin | xmax | id

------+------+-----

1571 | 0 | 100

1572 | 0 | 200

 1573 | 0 | 300

(3 lines)

2) We see the insertion of the third line. Let's take a look status transactions :

postgres=*# SELECT pg_xact_status( '1573' );

pg_xact_status

----------------

in progress

(1 line )

3) Let's commit the transaction and check the status:

postgres=*# commit;

COMMIT

postgres=# SELECT pg_xact_status( '1573' );

pg_xact_status

----------------

committed

(1 line )

CLOG behaves when a transaction is rolled back:

postgres=# begin;

BEGIN

postgres=*# INSERT INTO a VALUES(400);   

INSERT 0 1

postgres=*# select xmin, xmax, * from a;

xmin | xmax | id

------+------+-----

1571 | 0 | 100

1572 | 0 | 200

1573 | 0 | 300

1574 | 0 | 400

(4 lines )

postgres=*# SELECT pg_xact_status( '1574' );

pg_xact_status

----------------

in progress

(1 line )

postgres=*# ROLLBACK;

ROLLBACK

postgres=# SELECT pg_xact_status( '1574' );

pg_xact_status

----------------

aborted

(1 line )

postgres=*# Select xmin, xmax, * from a;

xmin | xmax | id

------+------+-----

1571 | 0 | 100

1572 | 0 | 200

1573 | 0 | 300

(3 lines )

Part 4. Table Locks

1) In the first transaction, insert a new row and look at the locks using pg_locks . To do this, we need the PID of the service process:

postgres=# SELECT pg_backend_pid();

pg_backend_pid

----------------

          12193

(1 line)

2) Open the transaction and refer to the table:

postgres=# begin;

BEGIN

postgres=*# UPDATE a SET id = id + 1;               

UPDATE 3

postgres=*# SELECT locktype, transactionid, mode, relation::regclass as obj FROM pg_locks where pid = 12193 ;

locktype | transactionid | mode | obj

---------------+--------------+------------------+----------

relation | | AccessShareLock | pg_locks

relation | | RowExclusiveLock | a

virtualxid | | ExclusiveLock |

 transactionid | 1577 | ExclusiveLock |

(4 lines)

A table-level lock called RowExclusiveLock has been added. - is imposed in case of updating rows.

3) In the second window, we will build an index on the table, first looking at the process PID .

postgres=# SELECT pg_backend_pid();

pg_backend_pid

----------------

          17210

(1 line )

postgres=# CREATE INDEX ON a (id);

4) The transaction is stuck. Let's look at what's happening in the second process in the first terminal.

postgres=*# SELECT locktype, transactionid, mode, relation::regclass as obj FROM pg_locks where pid = 17210 ; 

locktype | transactionid | mode | obj

------------+--------------+---------------+-----

virtualxid | | ExclusiveLock |

relation | | ShareLock | a

(2 lines )

Appeared blocking ShareLock , she Not compatible With RowExclusiveLock , arose blocking situation .

5) Let's fix it first transaction :

postgres=*# commit;

COMMIT

6) The command in the second window is immediately triggered:

CREATE INDEX

Part 5. Row locking

1) Let's start the first transaction:

postgres=# begin;

BEGIN

postgres=*# UPDATE a SET id = id + 1 WHERE id=101; 

UPDATE 1

2) Let's begin the second transaction :

postgres=# begin;

BEGIN

postgres=*# UPDATE a SET id = id + 1 WHERE id=101;

The transaction is stuck and a lock has been triggered.

3) Let's commit the first transaction:

postgres=*# commit;

COMMIT

The second one comes into play immediately.

UPDATE 0

postgres=*# commit;

COMMIT

Please note that the update did not occur, there is no such row version to update now.

4) In the first terminal, let's look at the table:

postgres=# Select xmin, xmax, * from a;

xmin | xmax | id

------+------+-----

1577 | 0 | 201

1577 | 0 | 301

 1579 | 1580 | 102

(3 lines )

5) Delete table : 

postgres=# DROP TABLE a;

DROP TABLE

Chapter 2c. Routine Maintenance

Part 1. Regular table cleaning

1) Run psql:

postgres@tantor:~$ psql -q

postgres=#

2) Let's create arbitrary table :

postgres=# CREATE TABLE a (id integer primary key generated always as identity, t char(2000)) WITH (autovacuum_enabled = off);

CREATE TABLE

postgres=# INSERT INTO a(t) SELECT to_char(generate_series(1,10000),'9999');

INSERT 0 10000

3) Let's see what happened:

postgres=# \da

                               Table "public.a"

Column | Type | Collation | Nullable | Default

--------+-----------------+-----------+----------+------------------------------

id | integer | | not null | generated always as identity

t | character(2000) | | |

Indexes:

"a_pkey" PRIMARY KEY, btree (id)

Please note: a primary key and index have been created.

4) Find out the size of the table and index in bytes:

postgres=# SELECT pg_table_size('a');

pg_table_size

---------------

20512768

(1 line )

postgres=# SELECT pg_table_size(' a_pkey ');

pg_table_size

---------------

245760

(1 line)

5) Update 50% of the rows:

postgres=# UPDATE a set t= t || 'a' where id > 5000;

UPDATE 5000

6) Let's see dimensions objects :

postgres=# SELECT pg_table_size('a');               

pg_table_size

---------------

30752768

(1 line )

postgres=# SELECT pg_table_size('a_pkey');

pg_table_size

---------------

360448

(1 line)

7) They have also increased. Let's clear the table and index:

postgres=# VACUUM a;

VACUUM

postgres=# SELECT pg_table_size('a') ; SELECT pg_table_size('a_pkey');

pg_table_size

---------------

30760960

(1 line )

pg_table_size

---------------

360448

(1 line)

8) The size remains the same. More once let's update lines :

postgres=# UPDATE a set t= t || 'a' where id > 5000;

UPDATE 5000

postgres=# SELECT pg_table_size('a'); SELECT pg_table_size('a_pkey');

pg_table_size

---------------

30760960

(1 line )

pg_table_size

---------------

360448

(1 line)

Again, the size didn't change. This happened because the cleared space was used.

9) For example, let's assume that a cleaning cycle is missed:

postgres=# UPDATE a set t= t || 'a' where id > 5000;                 

UPDATE 5000

postgres=# UPDATE a set t= t || 'a' where id > 5000;

UPDATE 5000

postgres=# SELECT pg_table_size('a');

SELECT pg_table_size('a_pkey');

pg_table_size

---------------

51249152

(1 line )

pg_table_size

---------------

466944

(1 line)

10) The size of objects has increased again:

postgres=# VACUUM a;

VACUUM

postgres=# SELECT pg_table_size('a'); SELECT pg_table_size('a_pkey');

pg_table_size

---------------

51249152

(1 line )

pg_table_size

---------------

466944

(1 line)

Even after cleaning, the size does not decrease.

Part 2. Table Analysis

1) Since several update cycles have occurred, let's see how current the statistics remain. First, let's look at the system catalog:

postgres=# SELECT reltuples FROM pg_class WHERE relname='a';

reltuples

-----------

8333

(1 line)

We found that our table contains 8333 rows.

2) Now let's turn to To table :

postgres=# SELECT count(*) FROM a;

count

-------

10000

(1 line)

3) It turns out there are more lines. Statistics are always approximate. Let's call the second phase of the analysis:

postgres=# ANALYZE a;

ANALYZE

4) Now the statistics have become more accurate:

postgres=# SELECT reltuples FROM pg_class WHERE relname='a';

 reltuples

-----------

10000

(1 line)

Part 3. Rebuilding the index

1) Let's see what size the objects are:

postgres=# SELECT pg_table_size('a'); SELECT pg_table_size('a_pkey');

pg_table_size

---------------

51249152

(1 line )

pg_table_size

---------------

466944

(1 line)

2) Currently, the table has only one index. Let's rebuild it. his :

postgres=# REINDEX TABLE a;

REINDEX

postgres=# SELECT pg_table_size('a'); SELECT pg_table_size('a_pkey');

pg_table_size

---------------

51249152

(1 line )

pg_table_size

---------------

     245760

(1 line)

3) The index size has decreased, the table size has remained unchanged.

Part 4. Complete cleaning

postgres=# VACUUM FULL a;

VACUUM

1) Let's see size objects :

postgres=# SELECT pg_table_size('a');

SELECT pg_table_size('a_pkey');

pg_table_size

---------------

    20488192

(1 line )

pg_table_size

---------------

245760

(1 line)

The table size has been reduced.

2) Delete the table:

postgres=# DROP TABLE a;

DROP TABLE

The task is completed.

Part 5. HypoPG Expansion

1) Install the hypopg extension :

postgres=# CREATE EXTENSION hypopg;

CREATE EXTENSION

2) Create a table with test data:

postgres=# CREATE TABLE hypo AS SELECT id, 'line ' || id AS val FROM generate_series(1,10000) id;

SELECT 10000

3) The execution plan for selecting a single row is a sequential scan ( Seq Scan ). There are no index access methods, since there are no indexes:

postgres=# EXPLAIN SELECT * FROM hypo WHERE id = 1;

                       QUERY PLAN

------------------------------------------

 Seq Scan on hypo (cost=0.00..165.60 rows= 41 width=36)

   Filter: (id = 1)

(2 lines)

Why is the expected number of rows 41 and not 1? There are no statistics.

4) Collect statistics:

postgres=# vacuum analyze hypo;

VACUUM

postgres=# EXPLAIN SELECT * FROM hypo WHERE id = 1;

QUERY PLAN

------------------------------------------

Seq Scan on hypo (cost=0.00..180.00 rows= 1 width=13)

   Filter: (id = 1)

(2 lines)

Expected number of rows 1.

The task is to optimize the execution of this query. We assume that an index on the id column will speed up the query. We need to verify that the planner will use the index. If the planner doesn't use the index, then the assumption is incorrect and the index shouldn't be created. Creating an index is labor-intensive and time-consuming, and it takes up space. Before creating the index, we want to test the hypothesis that the planner will use it when executing the query being optimized.

5) To test the hypothesis, create a hypothetical index:

postgres=# SELECT * FROM hypopg_create_index('CREATE INDEX hypo_idx ON hypo (id)');

indexrelid | indexname

------------+----------------------

13495 | <13495>btree_hypo_id

(1 line )

The name of the hypothetical index is generated automatically, this is normal.

No actual index is created, the command is executed instantly.

6) Look at the list of hypothetical indices:

postgres=# SELECT * FROM hypopg_list_indexes;

 indexrelid | index_name | schema_name | table_name | am_name

------------+----------------------+-------------+------------+---------

13495 | <13495>btree_hypo_id | public | hypo | btree

(1 line)

What is the implementation plan now?

7) Perform command :

postgres=# EXPLAIN SELECT * FROM hypo WHERE id = 1;

QUERY PLAN

---------------------------------

 Index Scan using "<13495>btree_hypo_id" on hypo (cost=0.04..8.05 rows=1 width=13)

   Index Cond: (id = 1)

(2 lines)

The plan shows that the index will be used.

There is no real index, so the actual execution plan uses a table scan:

postgres=# EXPLAIN ( analyze ) SELECT * FROM hypo WHERE id = 1;

QUERY PLAN

-----------------------------------

 Seq Scan on hypo (cost=0.00..180.00 rows=1 width=13) (actual time=0.025..0.875 rows=1 loops=1)

Filter: (id = 1)

Rows Removed by Filter: 9999

Planning Time: 0.077 ms

Execution Time: 1.074 ms

(5 lines )

8) Create real index :

postgres=# CREATE UNIQUE INDEX hypo_id ON hypo(id);

CREATE INDEX

The implementation plan remains the same:

postgres=# EXPLAIN SELECT * FROM hypo WHERE id = 1;

QUERY PLAN

-----------------------------------------------

Index Scan using "<13495>btree_hypo_id" on hypo (cost=0.04..8.05 rows=1 width=13)

   Index Cond: (id = 1)

(2 lines)

9) Remove side effects:

postgres=# SELECT hypopg_reset() ;

hypopg_reset

--------------

(1 line)

The planner started using the created index:

postgres=# EXPLAIN SELECT * FROM hypo WHERE id = 1;

QUERY PLAN

-------------------------------------------

Index Scan using hypo_id on hypo (cost=0.29..8.30 rows=1 width=13)

   Index Cond: (id = 1)

(2 lines)

The extension allows you to hide real indexes from the planner:

postgres=# SELECT hypopg_hide_index('hypo_id'::regclass);

hypopg_hide_index

-------------------

t

(1 line )

Hiding is only effective within a session and does not affect the operation of other sessions.

Hypothetical indices also exist only within a session.

The hypothetical indices disappear:

postgres=# SELECT * FROM hypopg_list_indexes;

indexrelid | index_name | schema_name | table_name | am_name

------------+------------+------------ +-------------+----------

(0 lines)

The execution plan will use sequential scanning:

postgres=# EXPLAIN SELECT * FROM hypo WHERE id = 1;

QUERY PLAN

-------------------------------------------------------

Seq Scan on hypo (cost=0.00..180.00 rows=1 width=13)

   Filter: (id = 1)

(2 lines)

There is a view with a list of indexes hidden in this session:

postgres=# SELECT * FROM hypopg_hidden_indexes;

indexrelid | index_name | schema_name | table_name | am_name | is_hypo

------------+------------+------------ +------------+----------+---------

17402 | hypo_id | public | hypo | btree | f

(1 line)

10) Make sure that hidden indexes and hypothetical indexes exist only within a session:

postgres=# SELECT * FROM hypopg_create_index('CREATE INDEX hypo_idx ON hypo (id)');

indexrelid | indexname

------------+----------------------

13495 | <13495>btree_hypo_id

(1 line )

postgres=# SELECT * FROM hypopg_list_indexes;

indexrelid | index_name | schema_name | table_name | am_name

------------+--------------------- +-------------+------------+---------

13495 | <13495>btree_hypo_id | public | hypo | btree

(1 line )

postgres=# \q

postgres@tantor:~$ psql -q

11) Type "help" to get help:

postgres=# SELECT * FROM hypopg_list_indexes;

indexrelid | index_name | schema_name | table_name | am_name

------------+------------+------------ +-------------+----------

(0 lines )

postgres=# SELECT * FROM hypopg_hidden_indexes;

indexrelid | index_name | schema_name | table_name | am_name | is_hypo

------------+------------+------------ +------------+----------+---------

(0 lines)

Chapter 2d. Executing Queries

Part 1. Creating objects for queries

1) Run psql :

postgres @ tantor :~$ psql - q

2) Create a table and view:

postgres=# CREATE TABLE test (col1 integer, col2 integer, name text);

INSERT INTO test VALUES (1,2,'test1');

INSERT INTO test VALUES (3,4,'test2');

CREATE VIEW v_table AS SELECT * FROM test;

SELECT col1, col2 FROM v_table WHERE name='test1'::text ;

CREATE TABLE

INSERT 0 1

INSERT 0 1

CREATE VIEW

postgres=#

col1 | col2

------+------

1 | 2

(1 line )

Part 2. Sequential reading of table blocks ( Seq Scan )

1) Using the Explain command, we will look at the query execution plan:

postgres=# EXPLAIN SELECT col1, col2 FROM v_table WHERE name='test1'::text ;

QUERY PLAN

-----------------------------------------------------

Seq Scan on test (cost=0.00..25.00 rows=6 width=8)

Filter: (name = 'test1'::text)

(2 lines )

We see that a sequential read of the test table was used . That is, the view was expanded, and the data was retrieved directly from the table.

2) Apply the parameters analyze and buffers . These show that the request was actually executed and how many pages were affected.

postgres=# EXPLAIN( analyze, buffers, costs off, timing off) 

SELECT col1, col2 FROM v_table WHERE name='test1'::text ;

QUERY PLAN

------------------------------------------

Seq Scan on test (actual rows=1 loops=1)

Filter: (name = 'test1'::text)

Rows Removed by Filter: 1

Buffers: shared read=1

Planning Time: 0.063 ms

Execution Time: 9.569 ms

(6 lines )

Part 3. Returning data by index

1) Create an index on column col1 :

postgres=# CREATE INDEX ON test (col1);

CREATE INDEX

postgres=# \d test

           Table "public.test"

 Column | Type | Rule sorting |

---------+---------+--------------------+

col1 | integer | |

col2 | integer | |

name | text | |

Indexes :

   "test_col1_idx" btree (col1)

2) You can make sure that the index name is generated automatically,

let's add information To table :

postgres=# INSERT INTO test(col1, col2)

SELECT generate_series(3,1003), generate_series(4,1004);

INSERT 0 1001

3) Let's see what happens if we select a small number of rows. That is, the case where we have high selectivity and low cardinality:

postgres=# EXPLAIN( analyze, buffers, costs off, timing off )

SELECT col1, col2 FROM test WHERE col1<20;

QUERY PLAN

-------------------------------------

Index Scan using test_col1_idx on test (actual rows=19 loops=1)

Index Cond: (col1 < 20)

Buffers: shared hit=3

Planning:

Buffers: shared hit=17

Planning Time: 0.179 ms

 Execution Time: 0.117 ms

(7 lines)

We made sure that index access is used.

Part 4. Low selectivity

Now let's select a large number of lines:

postgres=# SELECT count(*) FROM test;

count

-------

1003

(1 line )

Total lines 1003

postgres=# EXPLAIN ( analyze, buffers, costs off, timing off )

SELECT col1, col2 FROM test WHERE col1>20;

QUERY PLAN

-------------------------------------------

Seq Scan on test ( actual rows=983 loops=1)

Filter: (col1 > 20)

Rows Removed by Filter: 20

Buffers: shared hit=5

Planning:

Buffers: shared hit=3

Planning Time: 0.157 ms

Execution Time: 0.201 ms

(8 lines)

983 rows were selected, which means low selectivity and high cardinality.

We have seen that in this case index access becomes expensive, and PostgreSQL switches to sequential access.

Part 5. Using statistics

For example, when filling the test table, the third column was empty. Let's see what percentage of the data will have a NULL value.

Let's recollect the statistics:

postgres=# ANALYZE test;

ANALYZE

postgres=# SELECT stanulfrac FROM pg_statistic WHERE starelid = 'test'::regclass AND staattnum = 3;

stanullfrac

-------------

0.9981884

(1 row)

NULL value in more than 99% of rows.

Part 6. pg_stat_statements View

1) Install extensions:

postgres=# create extension pg_stat_statements;

create extension pg_store_plans;

\x \dx pg_stat_statements \x

CREATE EXTENSION

CREATE EXTENSION

Expanded display is on.

List of installed extensions

-[ RECORD 1 ]---+-------

Name | pg_stat_statements

Version | 1.12

Default version | 1.12

Schema | public

Description | track planning and execution statistics of all SQL statements executed

Expanded display is off .

2) Let's see what columns are in the view.

postgres=# \d pg_stat_statements

 View "public.pg_stat_statements"

        Column | Type |

------------------------+------------------+

userid | oid |

dbid | oid |

toplevel | boolean |

queryid | bigint |

query | text |

plans | bigint |

total_plan_time | double precision |

min_plan_time | double precision |

max_plan_time | double precision |

mean_plan_time | double precision |

stddev_plan_time | double precision |

calls | bigint |

total_exec_time | double precision |

min_exec_time | double precision |

max_exec_time | double precision |

mean_exec_time | double precision |

stddev_exec_time | double precision |

rows | bigint |

shared_blks_hit | bigint |

shared_blks_read | bigint |

shared_blks_dirtied | bigint |

shared_blks_written | bigint |

local_blks_hit | bigint |

local_blks_read | bigint |

local_blks_dirtied | bigint |

local_blks_written | bigint |

temp_blks_read | bigint |

temp_blks_written | bigint |

blk_read_time | double precision |

blk_write_time | double precision |

temp_blk_read_time | double precision |

temp_blk_write_time | double precision |

wal_records | bigint |

wal_fpi | bigint |

wal_bytes | numeric |

...

stats_since | timestamp with time zone

minmax_stats_since | timestamp with time zone

3) Reset the statistics collected by the extension:

postgres=# SELECT pg_stat_statements_reset();

pg_stat_statements_reset

--------------------------

(1 line )

4) Let's turn To table test :

postgres=# EXPLAIN (analyze) SELECT col1, col2 FROM test WHERE col1>20;

QUERY PLAN

---------------------------------------

Seq Scan on test (cost=0.00..17.54 rows=984 width=8) (actual time=0.022..0.132 rows=983 loops=1)

Filter: (col1 > 20)

Rows Removed by Filter: 20

Planning Time: 0.190 ms

Execution Time: 0.234 ms

(5 lines)

5) Using the pg_stat_statements view , we can see how long the query took to execute and how many pages were used:

postgres=# SELECT queryid, substring(query FOR 100) as query, total_exec_time as ms, shared_blks_hit as blocks from pg_stat_statements WHERE query LIKE '%col1, col2%';

queryid | query | ms | blocks

------------- ---------+ ----------------------------------------- +----------+--------

-3250261183448805182 | EXPLAIN (analyze) +| 0.491265 | 11

| SELECT col1, col2 FROM test WHERE col1>$1 | |

(1 line)

Chapter 2 e . Extensions

Part 1. Determining the directory with extension files

1) Use the pg_config utility in the command line :

postgres@education:~$ pg_config --sharedir

/opt/tantor/db/18/share/postgresql

2) Add extension to the resulting path :

postgres@education:~$ ls -l /opt/tantor/db/18/share/postgresql/extension/

...

-rw-r--r-- 1 root root 31171 vector--0.8.0.sql

-rw-r--r-- 1 root root 145 vector.control

-rw-r--r-- 1 root root 944 xml2--1.0--1.1.sql

-rw-r--r-- 1 root root 2049 xml2--1.1.sql

-rw-r--r-- 1 root root 182 xml2.control

3) Run psql :

postgres @ tantor :~$ psql - q

postgres=#

4) Let's define the extension path using the pg_config() function :

postgres=# SELECT setting FROM pg_config() where name = 'SHAREDIR';

setting

------------------------------------

/opt/tantor/db/18/share/postgresql

(1 row)

Part 2. Viewing installed extensions

postgres =# \ dx

               List of installed extensions

Name | Version | Default ver| Schema | Description

--------------------+---------+------------+------------+

hypopg | 1.4.2 | 1.4.2 | public | Hypothetical indexes for PostgreSQL

pg_stat_statements | 1.12 | 1.12 | public | track planning and execution statistics of all SQL statements executed

pg_store_plans | 1.10.1 | 1.10.1 | public | track plan statistics of all SQL statements executed

plpgsql | 1.0 | 1.0 | pg_catalog | PL/pgSQL procedural language

(4 rows)

Part 3. Viewing available extensions

pg_available_extensions extension :

postgres=# SELECT * from pg_available_extensions;

name |default_version|installed_ver| comment

------------------+---------------+------------+---------

...

pg_columnar | 11.1-12 | | Hydra Columnar extension

pgrowlocks | 1.2 | | show row-level locking information

pg_ivm | 1.13 | | incremental view maintenance on PostgreSQL

pg_freespace | 1.3 | | examine the free space map (FSM)

plperl | 1.0 | | PL/Perl procedural language

pg_store_plans | 1.10.1 | | track plan statistics of all SQL statements executed

pg_buffercache | 1.6 | | examine the shared buffer cache

jsonb_plpython3u | 1.0 | | transform between jsonb and plpython3u

pgaudit | 18.0 | | provides auditing functionality

pltcl | 1.0 | | PL/Tcl procedural language

plperlu | 1.0 | | PL/PerlU untrusted procedural language

pg_throttle | 1.1.0 | | throttle resources usage

( 96 rows)

96 extensions available in the example .

Part 4. Installing and removing custom updates

1) Install the pg_surgery extension :

postgres=# CREATE EXTENSION pg_surgery ;

CREATE EXTENSION

postgres=# \dx

List of installed extensions

Name | Version | Default ver| Schema | Description

--------------------+---------+------------+-----------+---------------

hypopg | 1.4.2 | 1.4.2 | public | Hypothetical indexes for PostgreSQL

pg_stat_statements | 1.12 | 1.12 | public | track planning and execution statistics of all SQL statements executed

pg_store_plans | 1.10.1 | 1.10.1 | public | track plan statistics of all SQL statements executed

pg_surgery | 1.0 | 1.0 | public | extension to perform surgery on a damaged relation

plpgsql | 1.0 | 1.0 | pg_catalog | PL/pgSQL procedural language

(5 rows )

2) Let's look at the contents of the extension:

postgres=# \dx+ pg_surgery 

Objects in the "pg_surgery" extension

Description of the object

---------------------------------------

function heap_force_freeze(regclass,tid[])

function heap_force_kill(regclass,tid[])

(2 lines )

3) Delete extension :

postgres=# DROP EXTENSION pg_surgery;

DROP EXTENSION

Part 5. Viewing available extension versions. Updating to the latest version

1) Let's use the representation pg_available_extension_versions :

postgres=# SELECT name, version FROM pg_available_extension_versions WHERE name = 'pg_stat_kcache';

name | version

----------------+---------

pg_stat_kcache | 2.1.2

pg_stat_kcache | 2.2.2

pg_stat_kcache | 2.1.1

pg_stat_kcache | 2.2.0

pg_stat_kcache | 2.2.1

pg_stat_kcache | 2.1.0

pg_stat_kcache | 2.3.0

pg_stat_kcache | 2.2.3

pg_stat_kcache | 2.1.3

(9 rows)

2) Let's install version 2.2.2:

postgres=# CREATE EXTENSION pg_stat_kcache VERSION '2.2.2';

ERROR: required extension "pg_stat_statements" is not installed

HINT: Use CREATE EXTENSION ... CASCADE to install required extensions too.

postgres=# CREATE EXTENSION pg_stat_kcache VERSION '2.2.2' cascade;

NOTICE: installing required extension "pg_stat_statements"

postgres=# show shared_preload_libraries ;

shared_preload_libraries

--------------------------

 pg_stat_statements , pg_qualstats, pg_store_plans, pg_prewarm, pg_stat_kcache

(1 row )

The result may differ from the one shown. To load libraries, they must be specified in the configuration parameter.

If libraries pg_stat_statements , pg_stat_kcache​​​​​​​ are not loaded, then run the commands in this section. If loaded , then go to To to the next one point .

postgres=#

alter system set shared_preload_libraries = pg_stat_statements, pg_stat_kcache ;
ALTER SYSTEM

A space after the comma is required .

Changing this parameter requires restarting the instance. Stop the instance using the pg_ctl utility and restart it as a service:

postgres=# \q

postgres@tantor:~$ pg_ctl stop

waiting for server to shut down.... done

server stopped

postgres@tantor:~$ sudo systemctl start tantor-se-server-18

postgres@tantor:~$ psql -q

postgres=# CREATE EXTENSION pg_stat_kcache VERSION '2.2.2' cascade;

NOTICE: installing required extension "pg_stat_statements"

CREATE EXTENSION

3 ) Check that extensions pg_stat_kcache And pg_stat_statements are set :

postgres=# \dx pg_stat*  

List of installed extensions

Name | Version | Schema | Description

--------------------+---------+--------+-----------------

pg_stat_kcache | 2.2.2 | public | Kernel statistics gathering

pg_stat_statements | 1.12 | public | track planning and execution statistics

of all SQL statements executed

(2 rows)

4) Let's see if the pg_stat_kcache extension can be updated to version 2.3.0. Let 's use pg_extension_update_paths function :

postgres=# SELECT * FROM pg_extension_update_paths('pg_stat_kcache') where source = '2.2.2' and target='2.3.0';

source | target | path

--------+--------+---------------------

2.2.2 | 2.3.0 | 2.2.2--2.2.3--2.3.0

(1 row)

5) Update extension :

postgres=# ALTER EXTENSION pg_stat_kcache UPDATE TO "2.2.3";

ALTER EXTENSION

postgres=# ALTER EXTENSION pg_stat_kcache UPDATE;

ALTER EXTENSION

It would have been possible to execute only the last command, the result would have been the same.

6) Let's delete it extension :

postgres=# DROP EXTENSION pg_stat_kcache;

DROP EXTENSION

Part 6. External Data Wrappers

1) Let's see what external data wrappers (FDW) there are:

postgres=# SELECT * FROM pg_available_extensions

WHERE name LIKE '%fdw%';

name | default_version | installed_version | comment

-------------+-----------------+-------------------+--------------

file_fdw | 1.0 | | foreign-data wrapper for flat file access

postgres_fdw | 1.2 | | foreign-data wrapper for remote PostgreSQL servers

(2 lines )

2) Install the extension:

postgres=# CREATE EXTENSION postgres_fdw;

CREATE EXTENSION

postgres=# \dx postgres_fdw 

      List of installed extensions

Name | Version | Scheme | Description

--------------+--------+---------------------

postgres_fdw | 1.2 | public | foreign-data wrapper for remote PostgreSQL servers

(1 line)

3) Let's see what databases there are:

postgres =# \ l

List of databases

Owner | Encoding | Locale Provider | LC_COLLATE | LC_CTYPE | Permissions

-----------+----------+-----------+------------------+-------------+-------------+------------------

 postgres | postgres | UTF 8 | libc | ru _ RU . UTF -8 | ru _ RU . UTF -8 |

 template 0 | postgres | UTF 8 | libc | ru _ RU . UTF -8 | ru _ RU . UTF -8 | = c / postgres +

| | | | | | postgres = CTc / postgres

 template 1 | postgres | UTF 8 | libc | ru _ RU . UTF -8 | ru _ RU . UTF -8 | = c / postgres +

| | | | | | postgres=CTc/postgres

test_db | postgres | UTF8 | libc | ru_RU.UTF-8 | ru_RU.UTF-8 |

(4 lines)

If there is no test_db database , create it and test tables:

postgres=# create database test_db;

CREATE DATABASE

postgres=# \! pgbench -i -d test_db

dropping old tables...

NOTICE: table "pgbench_accounts" does not exist, skipping

NOTICE: table "pgbench_branches" does not exist, skipping

NOTICE: table "pgbench_history" does not exist, skipping

NOTICE: table "pgbench_tellers" does not exist, skipping

creating tables...

generating data (client-side)...

vacuuming...

creating primary keys...

done in 0.25 s (drop tables 0.00 s, create tables 0.00 s, client-side generate 0.14 s, vacuum 0.05 s, primary keys 0.05 s).

4) Let's connect and retrieve information from the test_db database. First, let's create a remote server object:

postgres=# CREATE SERVER test FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', port '5432', dbname 'test_db');

CREATE SERVER

postgres=# \des

List of third-party servers

Name | Owner | Third-Party Data Wrapper

------+----------+--------------------------

test | postgres | postgres_fdw

(1 line )

5) After this, create a user to log in as. There can be several user mappings:

postgres=# CREATE USER MAPPING FOR postgres SERVER test OPTIONS ( user 'postgres', password 'postgres' );

CREATE USER MAPPING

postgres=# \deu

User Mapping List

Server | Username

--------+------------------

test | postgres

(1 line)

6) Definitions of external tables (data is not copied or transferred) can be imported:

postgres=# IMPORT FOREIGN SCHEMA public LIMIT TO (pgbench_tellers) FROM SERVER test INTO public; 
IMPORT FOREIGN SCHEMA

postgres=# \det

List of foreign tables

Schema | Table | Server

--------+-----------------+--------

public | pgbench_tellers | test

(1 row )

7) We access this table as a regular table:

postgres=# SELECT * FROM pgbench_tellers limit 2;

tid | bid | tbalance | filler

-----+-----+----------+--------

1 | 1 | 0 |

2 | 1 | 0 |

(2 rows)

8) The description of an external table can be obtained in the same way as for a regular table:

postgres=# \d pgbench_tellers 

Foreign table "public.pgbench_tellers"

Column | Type | Collation | Nullable | FDW options

----------+--------------+-----------+-----------+-----------

tid | integer | | not null |(column_name 'tid')

bid | integer | | |(column_name 'bid')

tbalance | integer | | |(column_name 'tbalance')

filler | character(84) | | |(column_name 'filler')

Server: test

FDW options: (schema_name 'public', table_name 'pgbench_branches')

9) Let's see where it comes from they come data :

postgres=# EXPLAIN SELECT * FROM pgbench_tellers;

QUERY PLAN

---------------------------------------

Foreign Scan on pgbench_tellers (cost=100.00..157.74 rows= 217 width=352)

(1 row)

postgres=# EXPLAIN analyze SELECT * FROM pgbench_tellers;

QUERY PLAN

---------------------------------------

Foreign Scan on pgbench_tellers (cost=100.00..157.74 rows= 217 width=352) (actual time=0.415..0.428 rows= 10.00 loops=1)

Planning Time: 0.043 ms

Execution Time: 0.980 ms

(3 rows)

10) Delete the created objects:

postgres=# DROP FOREIGN TABLE pgbench_tellers ;

DROP FOREIGN TABLE

postgres=# DROP USER MAPPING FOR postgres server test;

Drop User Mapping

postgres=# DROP SERVER test;

DROP SERVER

postgres=# DROP EXTENSION postgres_fdw;

DROP EXTENSION

Chapter 3. Configuration

Part 1. Configuration Parameters Overview

1) How many configuration options are there?

postgres=# select count(*) from pg_settings;

 count

-------

462

(1 line)

2) How many parameters are there that are not related to libraries and applications? Complete request :

postgres=# select count(name) from pg_settings where name not like '% . %';

 count

-------

429

(1 line)

Parameters with a period in their name refer to extensions, libraries, and applications (customized options, non-system parameters, user settings), and there can be any number of them. Loaded modules can register their configuration parameters.

To load libraries, you need to specify them in the configuration parameter. Run commands :

postgres=#

alter system set shared_preload_libraries = pg_stat_statements, pg_qualstats, pg_store_plans, pg_prewarm, pg_stat_kcache;
ALTER SYSTEM

A space after commas is required .

Changing this parameter requires restarting the instance. Stop the instance using the pg_ctl utility and restart it as a service:

postgres=# alter system set logging_collector = on;

alter system set log_filename = 'postgresql-%F.log';

alter system set max_wal_size = '512MB';

\q

postgres@tantor:~$ pg_ctl stop

waiting for server to shut down.... done

server stopped

postgres@tantor:~$ sudo systemctl start tantor-se-server-18

postgres@tantor:~$ psql -q

3) What libraries were loaded ?

postgres=# show shared_ pre load _libraries;

shared_preload_libraries

----------------------------------------

pg_stat_statements, pg_qualstats, pg_store_plans, pg_prewarm, pg_stat_kcache

(1 line)

Five libraries were loaded .

4) How many module (library) and application parameters are there? These parameters have a period in their names . Run request :

postgres=# select distinct split_part(name,' . ', 1), count(name) from pg_settings where name like '% . %' group by split_part(name,' . ', 1) order by 1;

split_part | count

--------------------+-------

pg_prewarm |     2
pg_qualstats |     8 pg_stat_kcache |     3 pg_stat_statements |     8 pg_store_plans |    1 2

(5 rows)

5) What are the maximum values of the parameters? This is interesting to compare the name of the parameter type with its dimension ( how many byte occupies value ). Perform request :

postgres=# select vartype, min_val, max_val, count(name) from pg_settings group by vartype, min_val, max_val order by length(max_val) desc, vartype;

vartype | min_val | max_val | count

---------+-------------+---------------------+-------

bool | | | 122

enum | | | 44

string | | | 68

 int64 | 0 | 9223372036854775807 | 1

 int64 | 100000 | 9223372036854775807 | 1

 int64 | -1 | 9223372036854775807 | 1

 int64 | 10000 | 9223372036854775807 | 4

real | -1 | 1.79769e+308 | 3

real | 0 | 1.79769e+308 | 7

int64 | 0 | 2100000000 | 2

integer | 100 | 1073741823 | 2

integer | -1 | 2147483647 | 13

integer | 1 | 2147483647 | 6

integer | -2147483648 | 2147483647 | 1

 integer | -1 | 1073741823 | 2

...

To scroll through lines in the terminal, you can use the < Shift >< Pg Up > and < Shift >< Pg Down > keys . The maximum value of the type named int 64 is 9223372036854775807 = 2 to the power of 63 minus 1, which corresponds to the maximum for a 64- bit signed integer type .

Types named integer have a maximum value of 2147483647, which is the maximum for a 32-bit signed integer type.

6) A context indicates whether a parameter's value can be changed, and if so, how . What parameter contexts are there, and how many parameters are in each context?

postgres=# select context , count(name) from pg_settings where name not like '%.%' group by context order by 1;

      context | count

-------------------+-------

 backend | 2

internal | 20

 postmaster | 75

sighup | 106

 superuser | 50

superuser-backend | 4

 user | 172

(7 rows)

Most context parameters user . Changes to context parameters postmaster will require an instance restart. Context parameters Internal context parameters are read-only (they cannot be changed by SET, ALTER SYSTEM commands , or by setting values in configuration parameter files) and there is no point in specifying them in configuration parameter files. Can the values of context parameters be changed? Internal ? Yes, they can. The method for changing depends on the parameter. For example, the value of the wal_segment_size parameter can be changed using the pg_resetwal utility. parameter data_checksums - utility pg_checksums .

7) Look at what categories of parameters there are:

postgres=# select category, count(*) from pg_settings group by category order by 2 desc;

category | count

------------------------------------------ -----------------------+-------

 Customized Options | 34

Query Tuning / Planner Method Configuration | 28

Resource Usage / Memory | 28

Client Connection Defaults/Statement Behavior | 27

Developer Options | 27

Reporting and Logging / What to Log | 22

Preset Options | 20

Query Tuning / Planner Cost Constants | 17

Query Tuning / Other Planner Options | 17

Vacuuming / Automatic Vacuuming | 15

Connections and Authentication / SSL | 15

Write-Ahead Log/Settings | 15

Replication / Standby Servers | 14

Reporting and Logging / Where to Log | 13

Client Connection Defaults / Locale and Formatting | 12

Connections and Authentication / Connection Settings | 11

Resource Usage / I/O | 8

Connections and Authentication / Authentication | 8

Write-Ahead Log/Recovery Target | 8

Statistics / Cumulative Query and Index Statistics | 8

Query Tuning / Genetic Query Optimizer | 7

Reporting and Logging / When to Log | 7

Vacuuming / Freezing | 7

Replication / Sending Servers | 7

Version and Platform Compatibility / Previous PostgreSQL Versions | 7

Write-Ahead Log/Checkpoints | 6

Connections and Authentication / TCP Settings | 5

File Locations | 5

Lock Management | 5

Statistics / Monitoring | 5

Resource Usage / Worker Processes | 5

Vacuuming/Cost-Based Vacuum Delay | 5

Error Handling | 4

Client Connection Defaults / Shared Library Preloading | 4

Replication / Subscribers | 4

Resource Usage/Background Writer | 4

Resource Usage / Disk | 4

Write-Ahead Log/Archiving | 4

Client Connection Defaults / Other Defaults | 4

Write-Ahead Log/Archive Recovery | 3

Write-Ahead Log/Summarization | 2

Reporting and Logging / Process Title | 2

Ungrouped | 2

Write-Ahead Log/Recovery | 2

Version and Platform Compatibility / Other Platforms and Clients | 2

Replication/Primary Server | 2

Resource Usage / Kernel Resources | 1

Vacuuming / Default Behavior | 1

(48 rows)

Category Customized Options - this is parameters extensions And applications .

8) How many parameters are set in the configuration parameter files?

postgres=# select sourcefile, count(*) from pg_settings group by sourcefile;

sourcefile | count

--------------------------------------- --------------------+-------

| 44 5

 /var/lib/postgresql/tantor-se-18/data/postgresql.auto.conf | 4

 /var/lib/postgresql/tantor-se-18/data/postgresql.conf | 1 3

(3 rows )

The configuration files contain 13 +4=17 parameters, and 445 parameters are unset (meaning they have default values). This result is an example; your values may be different .

9) In the postgresql.conf file , many parameters are commented out and uncommented. Comments provide short, high-quality, and convenient (at-hand) reference information.

What configuration parameters were read from the main parameter file when the instance started?

postgres=# select name, setting, sourceline from pg_settings where sourcefile like ' %l.conf ' order by sourceline ;

name | setting | sourceline 

----------------------------+--------------------+-----------

max_connections | 100 | 68

shared_buffers | 16384 | 136

dynamic_shared_memory_type | posix | 161

min_wal_size | 80 | 278

log_timezone | Europe/Moscow | 667

autovacuum_worker_slots | 16 | 709

DateStyle | ISO, MDY | 804

TimeZone | Europe/Moscow | 806

lc_messages | en_US.UTF-8 | 820

lc_monetary | en_US.UTF-8 | 822

lc_numeric | en_US.UTF-8 | 823

lc_time | en_US.UTF-8 | 824

default_text_search_config | pg_catalog.english | 830

( 13 rows )

sourceline - line number from the beginning of the file. The line number is convenient for finding and editing the parameter.

The same information can be viewed in the pg_file_settings view .

10) Execute command :

postgres=# select name, setting, sourceline, applied from pg_file_settings where sourcefile like ' %l.conf ';

name | setting | sourceline | applied 

----------------------------+--------------------+------------+---------

max_connections | 100 | 68 | t

shared_buffers | 128MB | 136 | t

dynamic_shared_memory_type | posix | 161 | t

max_wal_size | 1GB | 277 | f

min_wal_size | 80MB | 278 | t

log_timezone | Europe/Moscow | 667 | t

autovacuum_worker_slots | 16 | 709 | t

datestyle | iso, mdy | 804 | t

timezone | Europe/Moscow | 806 | t

lc_messages | en_US.UTF-8 | 820 | t

lc_monetary | en_US.UTF-8 | 822 | t

lc_numeric | en_US.UTF-8 | 823 | t

lc_time | en_US.UTF-8 | 824 | t

default_text_search_config | pg_catalog.english | 830 | t

( 14 rows )

You may not have any discrepancies, or they may be in other parameters.

What could be the reason for the discrepancy between the number of rows in the query from point 9 ( 13 rows ) and in this example ( 14 rows )? The pg_file_settings view contains the max_wal_size parameter . This parameter in the example is set in the postgresql.auto.conf file .

11) Example of file contents:

postgres=# \! cat $PGDATA/postgresql.conf | grep max_wal_size

max_wal_size = 1GB

postgres=# \! cat $PGDATA/postgresql.auto.conf

# Do not edit this file manually!

# It will be overwritten by the ALTER SYSTEM command.

shared_preload_libraries = 'pg_stat_statements, pg_qualstats, pg_store_plans, pg_prewarm, pg_stat_kcache'

logging_collector = 'on'

log_filename = 'postgresql-%F.log'

max_wal_size = '512MB'

IN both files present parameter max_wal_size . The pg_file_settings view displays all uncommented settings from all files. The applied column Line 277 has " f ". This means that this line is overridden by a subsequent line with the same parameter name (or by a line in the postgresql.auto.conf file whose contents override values from postgresql.conf ) . In the example , line 277 is overridden by line 4 from the postgresql.auto.conf file . We did not display the contents of this ( postgresql.auto.conf ) file in our queries ( sourcefile predicate like ' %l.conf ' ).

12) Views often have many columns, and when output line by line, they don't fit in the terminal. You can use extended output mode. Run the following commands:

postgres =# select * from pg_settings​​ where name = ' max_wal_size ' \ gx​​​

-[ RECORD 1 ]---+---------------

name | max_wal_size

setting | 512

unit | MB

category | Write-Ahead Log/Checkpoints

short_desc | Sets the WAL size that triggers a checkpoint.

extra_desc |

context | sighup

vartype | integer

source | configuration file

min_val | 2

max_val | 2147483647

enumvals |

boot_val | 1024

reset_val | 512

sourcefile | /var/lib/postgresql/tantor-se-18/data/postgresql.auto.conf​

sourceline | 6

pending_restart | f

In this example, all the details of the parameter are visible: category, short description, context.

The parameter value was applied from line 6 of the postgresql.auto.conf file .

13) Example of outputting values without a predicate (filter):

postgres=# select name, setting, substring(sourcefile, 39) file, sourceline, applied from pg_file_settings where name='max_wal_size';

name | setting | file | sourceline | applied

--------------+---------+----------------------+------------+---------

max_wal_size | 1GB | postgresql.conf | 277 | f

max_wal_size | 512MB | postgresql.auto.conf | 6 | t

(2 lines)

pg_file_settings view displays all lines in configuration parameter files where parameter values are set (uncommented and non-empty lines). For each parameter, there may be multiple lines specifying the parameter's value. This is not an error, but should be avoided (to avoid ambiguity).

When an instance is started (and files are reread if the parameter value can be changed without restarting the instance), the value from the postgresql.auto.conf file is applied. which comes last. This file may also contain repetitions, which appear when editing the file manually, as well as as a result of running utilities (for example, pg_basebackup ), which simply append lines to the end of the file, knowing that the one set to the end of the file will prevail.

If the parameter is missing from the postgresql.auto.conf file , the value closer to the end of the postgresql.conf file is used .

pg_settings view shows one row for each setting, that is, the one that is applied or can be applied.

In the column pending_restart value "t" will appear if the parameter value was changed in the configuration parameter file, the files were reread ( without rereading, the contents of pg_settings do not change ), and after rereading, an instance restart is required (that is, for the parameter context=postmaster ). In all other cases, the value pending_restart= "f" .

Unlike pg_settings view pg_file_settings shows the current contents of the parameter files, and the error column shows whether there were any errors after editing the files that would prevent the instance from starting.

14) There are no errors in these two configuration parameter files if a query like
select sourcefile, sourceline, error from pg_file_settings where error is not null does not return a single line .

If a query returns a single row, this doesn't mean there's an error in just one row; there could be multiple errors. After fixing the error, you should repeat the query, ensuring that it returns no rows. In many cases, the presence of an error will prevent the instance from starting after it's stopped. A non-empty value in the error column may not indicate an error, for example, the setting message. could not be applied means that the parameter is not applied (an example will be discussed in paragraph 3 of the next part of the practice).

Examples ( you do not need to execute the commands in this section ):

Error in parameter value:

postgres=# \! cat $PGDATA/postgresql.auto.conf | grep max_wal

max_wal_size = '512 m B'

postgres=# select substring(sourcefile, 39) file, sourceline, error from pg_file_settings where error is not null;

file | sourceline | error

----------------------+------------+------------------------------

postgresql.auto.conf | 4 | setting could not be applied

(1 line)

Without fixing the previous error, an error was added to the parameter name:

postgres=# \! cat $PGDATA/postgresql.conf | grep 512MB

max_w o l_size = 512MB

postgres=# select substring(sourcefile, 39) file, sourceline, error from pg_file_settings where error is not null;

file | sourceline | error

-----------------+------------+--------------------------------------

postgresql.conf | 836 | unrecognized configuration parameter

(1 line)

Without fixing the previous errors, an error was added to the line syntax:

postgres=# \! cat $PGDATA/postgresql.conf | grep max_wol

max_wol_size = 512MB

max_wol_size - 512 MB

postgres=# select substring(sourcefile, 39) file, sourceline, error from pg_file_settings where error is not null;

file | sourceline | error

-----------------+------------+--------------

postgresql.conf | 837 | syntax error

(1 line)

If any of the following errors are present, the instance will fail to start after stopping or during a restart:

postgres@tantor:~$ sudo systemctl restart tantor-se-server-18

Job for tantor-se-server-18.service failed because the control process exited with error code.

See "systemctl status tantor-se-server-18.service" and "journalctl -xe" for details.

"Setting could not be applied" errors do not always mean that the instance cannot be started.

Part 2. Configuration parameters with units of measurement

1) Let's see how to change the value of parameters with units of measurement.

Look at the properties of the shared_buffers parameter :

postgres=# select * from pg_settings where name = 'shared_buffers' \gx

-[ RECORD 1 ]---+-------------------

name | shared_buffers

setting | 16384

unit | 8kB

category | Resource Usage/Memory

short_desc | Sets the number of shared memory buffers used by the server.

extra_desc |

context | postmaster

vartype | integer

source | configuration file

min_val | 16

max_val | 1073741823

enumvals |

boot_val | 16384

reset_val | 16384

sourcefile | /var/lib/postgresql/tantor-se-18/data/postgresql.conf

sourceline | 136

pending_restart | f

8Kb blocks . The parameter is an integer .

2) Set the value for this parameter to 12800:

postgres=# alter system set shared_buffers = 12800 ;

ALTER SYSTEM

3) Check what was written to the parameters file:

postgres=# \! sudo -u postgres cat $PGDATA/postgresql.auto.conf

# Do not edit this file manually!

# It will be overwritten by the ALTER SYSTEM command.

logging_collector = 'on'

shared_preload_libraries = 'pg_stat_statements, pg_qualstats, pg_store_plans, pg_prewarm, pg_stat_kcache'

logging_collector = 'on'

log_filename = 'postgresql-%F.log'

max_wal_size = '512MB'

shared_buffers = ' 12800 '

The value was entered without apostrophes, in the file it was set with apostrophes .

4) Check if there are any errors in the parameter value set:

postgres=# select substring(sourcefile,39) file, sourceline, error from pg_file_settings where error is not null;

file | sourceline | error

----------------------+------------+------------------------------

postgresql.auto.conf | 7 | setting could not be applied

(1 line)

The error message indicates that the parameter cannot be applied without restarting the instance. The parameter has a postmaster context , meaning changing the value requires restarting the instance.

4) Restart copy :

postgres=# \q

postgres@tantor:~$ sudo systemctl restart tantor-se-server-18

postgres @ tantor :~$ psql - q

5) Look at the value of the parameter after restarting the instance:

postgres=# show shared_buffers;

shared_buffers

----------------

100MB

(1 line)

The value is returned in megabytes. The value '12800' is set in the parameters file.

12800 * 8192 (8 KB) = 104857600. 104857600 / 1024 / 1024 = 100. 12800 blocks is exactly 100 MB.

6) Without units of measurement, this parameter is measured in blocks.

Let's set the value in megabytes. Run the command:

postgres=# alter system set shared_buffers = 100mb ;

ERROR: trailing junk after numeric literal at or near "100m"

LINE 1: alter system set shared_buffers = 100mb;

                                            ^

It doesn't work. Units are case sensitive. Try command :

postgres=# alter system set shared_buffers = 100MB ;

ERROR: trailing junk after numeric literal at or near "100MB"

LINE 1: alter system set shared_buffers = 100MB;

                                            ^

Not it works . Put it apostrophes :

postgres=# alter system set shared_buffers = '100MB' ;

ALTER SYSTEM

It worked.

7) Look at what was written to the file when entering a value with a unit of measurement and in apostrophes:

postgres=# \! sudo -u postgres cat $PGDATA/postgresql.auto.conf

# Do not edit this file manually!

# It will be overwritten by the ALTER SYSTEM command.

...

shared_buffer = ' 100 MB '​

You've run the command several times to better remember the special feature of entering parameter values with units of measurement: unit case is important and apostrophes are required. Without remembering this, people often try to enter a number for this parameter, intuitively assuming the value is specified in bytes (instead, it's in blocks), and get out of memory errors when restarting the instance.

There may be spaces between the number and the unit of measurement without causing errors.

For example ( to perform Not need to ):

postgres=# alter system set shared_buffers = '100 MB' ;

postgres=# \! sudo -u postgres cat $PGDATA/postgresql.auto.conf

# Do not edit this file manually!

# It will be overwritten by the ALTER SYSTEM command.

...

shared_buffers = '100 MB'

Spaces worsen readability .

8) Remove from postgresql.auto.conf parameter shared_buffers :

postgres=# alter system reset shared_buffers;

ALTER SYSTEM

postgres=# \! sudo -u postgres cat $PGDATA/postgresql.auto.conf

# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
shared_preload_libraries = 'pg_stat_statements, pg_qualstats, pg_store_plans, pg_prewarm, pg_stat_kcache'

logging_collector = 'on'

log_filename = 'postgresql-%F.log'

max_wal_size = ' 512 MB '​​

All lines with the shared_buffers parameter (even if there were several lines with the parameter, which can happen when editing the file manually) will disappear.

9) Restart copy :

postgres=# \q

postgres@tantor:~$ sudo systemctl restart tantor-se-server-18

postgres @ tantor :~$ psql - q

In this section, we learned how to remove parameters from postgresql.auto.conf.

Part 3. Configuration parameters of the logical type

1) Let's look at the logical type parameter (bool) :

postgres=# select * from pg_settings where name = 'hot_standby_feedback' \gx

-[ RECORD 1 ]---+--------------------------------

name | hot_standby_feedback

setting | off

unit |

category | Replication/Standby Servers

short_desc | Allows feedback from a hot standby to the primary that will avoid query conflicts.

extra_desc |

context | sighup

vartype | bool

source | default

min_val |

max_val |

enumvals |

boot_val | off

reset_val | off

sourcefile |

sourceline |

pending_restart | f

sighup context means that re-reading the configuration files is sufficient to apply the new value.

2) "Turn on" the parameter, that is, set the value to true :

postgres=# alter system set hot_standby_feedback = o ;

ERROR: parameter "hot_standby_feedback" requires a Boolean value

The error means that the value cannot be reduced because there is an ambiguity. quality values allowed o n And o ff:

postgres=# alter system set hot_standby_feedback = on ;

ALTER SYSTEM

The value on is valid for Boolean parameters. Check that other values are also valid:

postgres=# alter system set hot_standby_feedback = 1 ;

ALTER SYSTEM

postgres=# alter system set hot_standby_feedback = '1' ;

ALTER SYSTEM

One is also acceptable:

postgres=# alter system set hot_standby_feedback = tr ;

ALTER SYSTEM

Abbreviations of values are allowed, but only if there is no ambiguity.

The ambiguity was with the abbreviation to one letter " o ".

3) Look at what was written to the parameters file:

postgres=# \! sudo -u postgres cat $PGDATA/postgresql.auto.conf

# Do not edit this file manually!

# It will be overwritten by the ALTER SYSTEM command.

..

hot_standby_feedback = 'tr'

The abbreviated meaning was written in apostrophes.

Abbreviations are difficult to read. For logical parameters, it's better to use the canonical values on and off .

4) Reread the parameter files for the new value to take effect:

postgres=# select pg_reload_conf() ;

pg_reload_conf

----------------

t

(1 line )

postgres=# show hot_standby_feedback;

hot_standby_feedback

----------------------

on

(1 line )

The value has been set correctly.

5) Reset to default value :

postgres=# alter system reset hot_standby_feedback;

ALTER SYSTEM

postgres=# select pg_reload_conf() ;

pg_reload_conf

----------------

t

(1 line )

Part 4. Configuration parameters

"Configuration parameters" (settings) and "Configuration parameters" (config) are similar terms. In this part of the practice, we'll look at "Configuration parameters."

There are three ways to view configuration parameters: the pg_config command-line utility , the pg_config view , and the pg_config() function .

1) See what configuration parameters exist using the pg_config utility :

postgres@tantor:~$ pg_config --help

pg_config provides information about the installed PostgreSQL version.

Usage:

pg_config [PARAMETER]...

Parameters:

--bindir show location of executable files

--docdir show the location of documentation files

--htmldir Show the location of the HTML documentation files

--includedir show the location of header files (.h) for

client interfaces in C

--pkgincludedir show locations of other header files (.h)

--includedir-server Show the location of header files (.h) for the server

--libdir show the location of object code libraries

--pkglibdir show location of dynamically loaded modules

--localedir show location of locale description files

--mandir show location of manual pages

--sharedir show location of platform-independent files

--sysconfdir show the location of system-wide configuration files

--pgxs Show makefile location for extensions

--configure show the "configure" script parameters with which

PostgreSQL was compiled

--cc show what CC value PostgreSQL was compiled with

--cppflags show what CPPFLAGS value PostgreSQL was compiled with

--cflags Show which C flags PostgreSQL was compiled with

--cflags_sl show what CFLAGS_SL value PostgreSQL was compiled with

--ldflags Show what LDFLAGS value PostgreSQL was compiled with

--ldflags_ex show what LDFLAGS_EX value PostgreSQL was compiled with

--ldflags_sl show what LDFLAGS_SL value PostgreSQL was compiled with

--libs Show what LIBS value PostgreSQL was compiled with

--version show PostgreSQL version

-?, --help show this help and exit

When run without arguments, all known values are printed.

These parameters are set when building Tantor Postgres and are not changed. They are the same for all builds. BE, SE, SE1C . Since directory names are long and difficult to remember, the pg_config utility is useful because, given the utility name and the directory type, you can obtain the file system path to the desired directory.

2) Run the utility without parameters; the utility will display the values of all parameters:

postgres@tantor:~$ pg_config

BINDIR = /opt/tantor/db/18/bin

DOCDIR = /opt/tantor/db/18/share/doc/postgresql

HTMLDIR = /opt/tantor/db/18/share/doc/postgresql

INCLUDEDIR = /opt/tantor/db/18/include

PKGINCLUDEDIR = /opt/tantor/db/18/include/postgresql

INCLUDEDIR-SERVER = /opt/tantor/db/18/include/postgresql/server

LIBDIR = /opt/tantor/db/18/lib

PKGLIBDIR = /opt/tantor/db/18/lib/postgresql

LOCALEDIR = /opt/tantor/db/18/share/locale

MANDIR = /opt/tantor/db/18/share/man

SHAREDIR = /opt/tantor/db/18/share/postgresql

SYSCONFDIR = /opt/tantor/db/18/etc/postgresql

PGXS = /opt/tantor/db/18/lib/postgresql/pgxs/src/makefiles/pgxs.mk

CONFIGURE = '--prefix=/opt/tantor/db/18' '--enable-tap-tests' '--enable-nls= en ru ' '--with-python' '--with-perl' '--with-tcl' ' --with-icu ' '--with-libcurl' '--with-lz4' '--with-zstd' '--with-ssl=openssl' '--with-ldap' '--with-pam' '--with-uuid=e2fs' '--with-libxml' '--with-libxslt' '--with-gssapi' '--with-selinux' '--with-systemd' '--with-llvm' 'CFLAGS=-pipe -O2' 'LDFLAGS=-Wl,-z,relro -Wl,-z,now -flto=auto -ffat-lto-objects' 'CPPFLAGS=-Wno-missing-braces -Wformat -Werror=format-security -fstack-protector-strong -U_FORTIFY_SOURCE -D_FORTIFY_SOURCE=2 -DTANTOR_SE' 'CXXFLAGS=-pipe -O2 -D_GLIBCXX_ASSERTIONS' 'LLVM_CONFIG=/usr/bin/llvm-config-13' 'CLANG=/usr/bin/ clang-13 ' 'ZSTD_CFLAGS=-I/usr/local/include/zstd' 'ZSTD_LIBS=-L/usr/local/lib/zstd -lzstd' 'PYTHON=/usr/bin/python3'

CC = gcc

CPPFLAGS = -Wno-missing-braces -Wformat -Werror=format-security -fstack-protector-strong -U_FORTIFY_SOURCE -D_FORTIFY_SOURCE=2 -DTANTOR_SE -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/local/include/zstd

CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -pipe -O2

CFLAGS_SL = -fPIC

LDFLAGS = -Wl,-z,relro -Wl,-z,now -flto=auto -ffat-lto-objects -L/usr/local/lib/zstd -Wl,--as-needed -Wl,-rpath,'/opt/tantor/db/18/lib',--enable-new-dtags

LDFLAGS_EX =

LDFLAGS_SL =

LIBS = -lpgcommon -lpgport -lselinux -lzstd -llz4 -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -lreadline -lm

VERSION = PostgreSQL 18.3

The location of the directory with external libraries (loadable modules, PKGLIBDIR ) is shown by the --pkglibdir parameter .

3) Libraries are loaded when the instance is started using the shared_preload_libraries configuration parameter or, if the library can be loaded not only when the instance is started but also dynamically by the server process, then using the LOAD 'library_name' command ;

See what libraries are available:

postgres@tantor:~$ ls $( pg_config --pkglibdir )

amcheck.so libpqwalreceiver.so pg_trgm.so

auth_delay.so llvmjit.so pg_uuidv7.so

auto_dump.so llvmjit_types.bc pg_variables.so

auto_explain.so lo.so pg_visibility.so

autoinc.so ltree_plpython3.so pg_wait_sampling.so

basebackup_to_shell.so ltree.so pg_walinspect.so

basic_archive.so mchar.so pgxml.so

bitcode moddatetime.so pgxs

bloom.so oauth_base_validator.so plantuner.so

bool_plperl.so online_analyze.so plperl.so

btree_gin.so orafce.so plpgsql.so

btree_gist.so pageinspect.so plpython3.so

citext.so page_repair.so pltcl.so

credcheck.so passwordcheck.so postgres_fdw.so

csn.so pg_archive_bgw.so refint.so

cube.so pgauditlogtofile.so seg.so

cyrillic_and_mic.so pgaudit.so sepgsql.so

dbcopies_decoding.so pg_background.so sslinfo.so

dblink.so pg_buffercache.so tablefunc.so

ddl_deparse.so pg_columnar.so tcn.so

dict_int.so pg_cron.so test_decoding.so

dict_snowball.so pgcrypto.so transp_anon.so

dict_xsyn.so pg_freespacemap.so tsm_system_rows.so

earthdistance.so pg_hint_plan.so tsm_system_time.so

euc2004_sjis2004.so pg_ivm.so unaccent.so

euc_cn_and_mic.so pgl_ddl_deploy.so utf8_and_big5.so

euc_jp_and_sjis.so pgoutput.so utf8_and_cyrillic.so

euc_kr_and_mic.so pg_partman_bgw.so utf8_and_euc2004.so

euc_tw_and_big5.so pg_prewarm.so utf8_and_euc_cn.so

fasttrun.so pgq_lowlevel.so utf8_and_euc_jp.so

file_fdw.so pgq_triggers.so utf8_and_euc_kr.so

fulleq.so pg_qualstats.so utf8_and_euc_tw.so

fuzzystrmatch.so pg_query_id.so utf8_and_gb18030.so

hstore_plperl.so pg_repack.so utf8_and_gbk.so

hstore_plpython3.so pgrowlocks.so utf8_and_iso8859_1.so

hstore.so pg_sample_profile.so utf8_and_iso8859.so

http.so pg_stat_advisor.so utf8_and_johab.so

hypopg.so pg_stat_kcache.so utf8_and_sjis2004.so

insert_username.so pg_stat_statements.so utf8_and_sjis.so

_int.so pgstattuple.so utf8_and_uhc.so

isn.so pg_store_plans.so utf8_and_win.so

jsonb_plperl.so pg_surgery.so uuid-ossp.so

jsonb_plpython3.so pg_tde.so vector.so

latin2_and_win1250.so pg_throttle.so wal2json.so

latin_and_mic.so pg_trace.so zcheck.so

4) Let's check that some shared libraries can be loaded dynamically. Load module pg_hint_plan :

postgres=# show pg_hint_plan.enable_hint;

ERROR: unrecognized configuration parameter "pg_hint_plan.enable_hint"

The server process is unaware of this parameter because the module was not loaded either by the server process or at the instance level. Load the module into the memory of the server process serving the current session:

postgres=# LOAD 'pg_hint_plan';

LOAD

The library has been loaded into the memory of the server process servicing the session in which the command was issued. The module's functionality can be used in this session.

5) In particular, module configuration parameters are now available in the session. When typing, you can use the <TAB> key on your keyboard. Psql will continue typing for you if there are no other options, and when you double-tap the key, it will display a list of possible values.

Type show pg_hint<TAB>.<TAB> :

postgres=# show pg_hint_plan.enable_hint ;

pg_hint_plan.enable_hint

--------------------------

 on

(1 line)

6) Let's use another option for viewing configuration parameters:

postgres=# \dconfig pg_hint_plan.*

      List of configuration parameters

Parameter | Value

--------------------------------+----------

pg_hint_plan.debug_print | off
pg_hint_plan.enable_hint | on pg_hint_plan.enable_hint_table | off pg_hint_plan.message_level | log
pg_hint_plan.parse_messages | info
(5 rows)

In version 18 , the pg_hint_plan.hints_anywhere parameter was removed .​

When installing extensions, dynamically linked libraries ( *.so ) are copied to the PKGLIBDIR directory if the extension contains shared libraries.

The second useful directory for managing extensions is SHAREDIR . Extension files are copied to this directory and then installed using the CREATE EXTENSION command .

7) Extensions are not a shared cluster object and are installed at the database level.

See which extensions are ready to be installed into your databases:

postgres@tantor:~$ ls $(pg_config --sharedir)/extension | grep .control

amcheck . control

...

zcheck.control​

8) The list of the same extensions can be viewed in the pg_available_extensions view :

postgres=# select count(*) from pg_available_extensions;

 count

-------

    96

There are quite a few extensions supplied with Tantor Postgres SE.

9) Look at the definition of the view:

postgres=# \sv pg_available_extensions

CREATE OR REPLACE VIEW pg_catalog.pg_available_extensions AS

SELECT e.name,

e.default_version,

x.extversion AS installed_version ,

e.comment

FROM pg_available_extensions() e(name, default_version, comment)

LEFT JOIN pg_extension x ON e.name = x.extname

The view uses the function pg_available_extensions(), which reads the contents of *.control files in the SHAREDIR directory .

9) Look at the function definition:

postgres=# \sf pg_available_extensions()

CREATE OR REPLACE FUNCTION pg_catalog.pg_available_extensions(OUT name name, OUT default_version text, OUT comment text)

RETURNS SETOFF record

LANGUAGE internal

STABLE PARALLEL SAFE STRICT COST 10 ROWS 100

AS $function$pg_available_extensions$function$

Team \sv - you can view the texts of the performances.

Team \sf - texts of subroutines, including the system catalog.

Part 5. Services file

If you have difficulty copying a file due to operating system privileges or editing files, you can skip this part of the practice and look at the examples below.

1) Look at which directory the SYSCONFDIR parameter points to . This directory contains the default files.

postgres@tantor:~$ pg_config --sysconfdir

/opt/tantor/db/18/etc/postgresql

2) Create directories:

postgres@tantor:~$ sudo mkdir /opt/tantor/db/18/etc

postgres@tantor:~$ sudo chown postgres:postgres /opt/tantor/db/18/etc

postgres@tantor:~$ mkdir /opt/tantor/db/18/etc/postgresql

3) Copy the example file to this directory ( command one line ):

postgres@tantor:~$ cp $(pg_config --sharedir)/pg_service.conf.sample $(pg_config --sysconfdir)/pg_service.conf

4) Look content file services :

postgres@tantor:~$ cat $(pg_config --sysconfdir)/pg_service.conf

#

# Connection configuration file

#

# A service is a set of named connection parameters. You may specify

# multiple services in this file. Each starts with a service name in

#brackets. Subsequent lines have connection configuration parameters of

# the pattern "param=value" or LDAP URLs starting with "ldap://"

# to look up such parameters. A sample configuration for postgres is

# included in this file. Lines beginning with '#' are comments.

#

# Copy this to your sysconf directory (typically /usr/local/pgsql/etc) and

# rename it pg_service.conf.

#

#

#[postgres]

#dbname=postgres

#user=postgres

5) Edit file /opt/tantor/db/18/etc/postgresql/pg_service.conf :

postgres@tantor:~$ mcedit /opt/tantor/db/18/etc/postgresql/pg_service.conf