### Short version: configure, make, install, and start PostgreSQL Source: https://www.postgresql.org/docs/current/install-make.html Quick sequence to build and install PostgreSQL from source, create a data directory, initialize the database, start the server, and connect with psql. Run as root where needed (su) and as the postgres user for initdb and pg_ctl. ```shell ./configure make su make install adduser postgres mkdir -p /usr/local/pgsql/data chown postgres /usr/local/pgsql/data su - postgres /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start /usr/local/pgsql/bin/createdb test /usr/local/pgsql/bin/psql test ``` -------------------------------- ### meson setup with prefix, debug buildtype, and OpenSSL options Source: https://www.postgresql.org/docs/current/install-meson.html Examples of passing build options to meson setup: a different installation prefix, a debug build, and OpenSSL support. ```shell # configure with a different installation prefix meson setup build --prefix=/home/user/pg-install # configure to generate a debug build meson setup build --buildtype=debug # configure to build with OpenSSL support meson setup build -Dssl=openssl ``` -------------------------------- ### Short version: build and install PostgreSQL with Meson Source: https://www.postgresql.org/docs/current/install-meson.html End-to-end sequence to configure, build, install, and start a PostgreSQL server using Meson and Ninja. Run the install steps as root and the initdb/pg_ctl steps as the postgres user. ```shell meson setup build --prefix=/usr/local/pgsql cd build ninja su ninja install adduser postgres mkdir -p /usr/local/pgsql/data chown postgres /usr/local/pgsql/data su - postgres /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start /usr/local/pgsql/bin/createdb test /usr/local/pgsql/bin/psql test ``` -------------------------------- ### Start the build with make or make all Source: https://www.postgresql.org/docs/current/install-make.html Build the server and utilities using GNU make. The build takes a few minutes depending on hardware. ```shell make make all ``` -------------------------------- ### Asynchronous Notification Example (testlibpq2.c) Source: https://www.postgresql.org/docs/current/libpq-example.html Demonstrates LISTEN/NOTIFY using libpq. Start the program, then from psql execute NOTIFY TBL2 four times to exit. Requires the testlibpq2.sql setup for the rule-based notification variant. ```c /* * src/test/examples/testlibpq2.c * * * testlibpq2.c * Test of the asynchronous notification interface * * Start this program, then from psql in another window do * NOTIFY TBL2; * Repeat four times to get this program to exit. * * Or, if you want to get fancy, try this: * populate a database with the following commands * (provided in src/test/examples/testlibpq2.sql): * * CREATE SCHEMA TESTLIBPQ2; * SET search_path = TESTLIBPQ2; * CREATE TABLE TBL1 (i int4); * CREATE TABLE TBL2 (i int4); * CREATE RULE r1 AS ON INSERT TO TBL1 DO * (INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2); * * Start this program, then from psql do this four times: * * INSERT INTO TESTLIBPQ2.TBL1 VALUES (10); */ #ifdef WIN32 #include #endif #include #include #include #include #include #include #include #include "libpq-fe.h" static void exit_nicely(PGconn *conn) { PQfinish(conn); exit(1); } int main(int argc, char **argv) { const char *conninfo; PGconn *conn; PGresult *res; PGnotify *notify; int nnotifies; /* * If the user supplies a parameter on the command line, use it as the * conninfo string; otherwise default to setting dbname=postgres and using * environment variables or defaults for all other connection parameters. */ if (argc > 1) conninfo = argv[1]; else conninfo = "dbname = postgres"; /* Make a connection to the database */ conn = PQconnectdb(conninfo); /* Check to see that the backend connection was successfully made */ if (PQstatus(conn) != CONNECTION_OK) { fprintf(stderr, "%s", PQerrorMessage(conn)); exit_nicely(conn); } /* Set always-secure search path, so malicious users can't take control. */ res = PQexec(conn, "SELECT pg_catalog.set_config('search_path', '', false)"); if (PQresultStatus(res) != PGRES_TUPLES_OK) { fprintf(stderr, "SET failed: %s", PQerrorMessage(conn)); PQclear(res); exit_nicely(conn); } /* * Should PQclear PGresult whenever it is no longer needed to avoid memory * leaks */ PQclear(res); /* * Issue LISTEN command to enable notifications from the rule's NOTIFY. */ res = PQexec(conn, "LISTEN TBL2"); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "LISTEN command failed: %s", PQerrorMessage(conn)); PQclear(res); exit_nicely(conn); } PQclear(res); /* Quit after four notifies are received. */ nnotifies = 0; while (nnotifies < 4) { /* * Sleep until something happens on the connection. We use select(2) * to wait for input, but you could also use poll() or similar * facilities. */ int sock; fd_set input_mask; sock = PQsocket(conn); if (sock < 0) break; /* shouldn't happen */ FD_ZERO(&input_mask); FD_SET(sock, &input_mask); if (select(sock + 1, &input_mask, NULL, NULL, NULL) < 0) { fprintf(stderr, "select() failed: %s\n", strerror(errno)); exit_nicely(conn); } /* Now check for input */ PQconsumeInput(conn); while ((notify = PQnotifies(conn)) != NULL) { fprintf(stderr, "ASYNC NOTIFY of '%s' received from backend PID %d\n", notify->relname, notify->be_pid); PQfreemem(notify); nnotifies++; PQconsumeInput(conn); } } fprintf(stderr, "Done.\n"); /* close the connection to the database and cleanup */ PQfinish(conn); return 0; } ``` -------------------------------- ### Create subscription mysub on the subscriber database Source: https://www.postgresql.org/docs/current/logical-replication-quick-setup.html Run on the subscriber database. This starts replication, synchronizing the initial contents of users and departments, then replicating incremental changes. ```sql CREATE SUBSCRIPTION mysub CONNECTION 'dbname=foo host=bar user=repuser' PUBLICATION mypub; ``` -------------------------------- ### Install PostgreSQL with ninja install Source: https://www.postgresql.org/docs/current/install-meson.html Run after building PostgreSQL to install files into the directories specified during meson setup. Ensure you have write permissions to the target area; you may need root. Use meson install instead for extra options like --quiet. ```shell ninja install ``` -------------------------------- ### Install documentation with make install-docs Source: https://www.postgresql.org/docs/current/install-make.html Installs the HTML and man page documentation. ```shell make install-docs ``` -------------------------------- ### Create text search configuration pg by copying english Source: https://www.postgresql.org/docs/current/textsearch-configuration.html Duplicates the built-in pg_catalog.english configuration into a new public.pg configuration as the starting point for the custom setup. ```sql CREATE TEXT SEARCH CONFIGURATION public.pg ( COPY = pg_catalog.english ); ``` -------------------------------- ### Start the server as the postgres user with su postgres -c Source: https://www.postgresql.org/docs/current/server-start.html The server must run under the PostgreSQL user account and not root, so commands are typically formed with su postgres -c. This example starts pg_ctl with an explicit data directory and server log. ```shell su postgres -c 'pg_ctl start -D /usr/local/pgsql/data -l serverlog' ``` -------------------------------- ### Start the database server with postgres Source: https://www.postgresql.org/docs/current/upgrading.html Starts the new database server using the special database user account. ```shell /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data ``` -------------------------------- ### Add PostgreSQL man directory to MANPATH Source: https://www.postgresql.org/docs/current/install-post.html Enables the system to find the man documentation unless PostgreSQL was installed into a location searched by default. Add to a shell start-up file. ```shell MANPATH=/usr/local/pgsql/share/man:$MANPATH export MANPATH ``` -------------------------------- ### Build and install sepgsql-regtest policy package Source: https://www.postgresql.org/docs/current/sepgsql.html Run from the contrib/sepgsql directory of a configured PostgreSQL build tree. The Makefile path is an example; locate the appropriate SELinux Makefile on your system. After installation, semodule -l should list sepgsql-regtest. ```shell $ cd .../contrib/sepgsql $ make -f /usr/share/selinux/devel/Makefile $ sudo semodule -u sepgsql-regtest.pp $ sudo semodule -l | grep sepgsql sepgsql-regtest 1.07 ``` -------------------------------- ### Install world with make install-world Source: https://www.postgresql.org/docs/current/install-make.html Use instead of make install if you built the world above; this also installs the documentation. ```shell make install-world ``` -------------------------------- ### Client-only installation commands Source: https://www.postgresql.org/docs/current/install-make.html Installs only client applications and interface libraries. src/bin contains a few small server-only binaries. ```shell make -C src/bin install make -C src/include install make -C src/interfaces install make -C doc install ``` -------------------------------- ### Run configure for a default installation Source: https://www.postgresql.org/docs/current/install-make.html First step of the installation procedure: configure the source tree for your system with default options. This creates files in the build tree recording detected system values. ```shell ./configure ``` -------------------------------- ### Install world without docs with make install-world-bin Source: https://www.postgresql.org/docs/current/install-make.html Use instead of make install if you built the world without the documentation above. ```shell make install-world-bin ``` -------------------------------- ### Install the postgres_fdw extension Source: https://www.postgresql.org/docs/current/postgres-fdw.html First step in setting up postgres_fdw: install the extension in the local database before creating any foreign servers or tables. ```sql CREATE EXTENSION postgres_fdw; ``` -------------------------------- ### Install PostgreSQL with make install Source: https://www.postgresql.org/docs/current/install-make.html Installs files into the directories specified during configuration. Requires appropriate write permissions, typically run as root. ```shell make install ``` -------------------------------- ### Start the PostgreSQL server with pg_ctl start Source: https://www.postgresql.org/docs/current/app-pg-ctl.html Starts the server and waits until it is accepting connections. Requires the data directory via -D or the PGDATA environment variable. ```shell $ pg_ctl start ``` -------------------------------- ### Set up hstore type and plpython3u language for a transform Source: https://www.postgresql.org/docs/current/sql-createtransform.html First step of the transform example: create the hstore type and install the plpython3u extension before defining the conversion functions. ```sql CREATE TYPE hstore ...; CREATE EXTENSION plpython3u; ``` -------------------------------- ### Shared library loading error message Source: https://www.postgresql.org/docs/current/install-post.html Example error indicating the shared library search path step was necessary. If you see this message, take care of setting the library path. ```text psql: error in loading shared libraries libpq.so.2.1: cannot open shared object file: No such file or directory ``` -------------------------------- ### Install new PostgreSQL binaries to custom prefix Source: https://www.postgresql.org/docs/current/pgupgrade.html For source installs, use the prefix variable to install the new server in a custom location. ```shell make prefix=/usr/local/pgsql.new install ``` -------------------------------- ### Add PostgreSQL bin directory to PATH in Bourne shells Source: https://www.postgresql.org/docs/current/install-post.html Makes PostgreSQL programs convenient to use when installed into a location not searched by default. Add to a shell start-up file such as ~/.bash_profile, or /etc/profile to affect all users. ```shell PATH=/usr/local/pgsql/bin:$PATH export PATH ``` -------------------------------- ### Example pg_restore listing file format Source: https://www.postgresql.org/docs/current/app-pgrestore.html Shows the header and per-item lines of a listing file. Semicolons start a comment, and the leading numbers are the internal archive IDs assigned to each item. ```text ; ; Archive created at Mon Sep 14 13:55:39 2009 ; dbname: DBDEMOS ; TOC Entries: 81 ; Compression: 9 ; Dump Version: 1.10-0 ; Format: CUSTOM ; Integer: 4 bytes ; Offset: 8 bytes ; Dumped from database version: 8.3.5 ; Dumped by pg_dump version: 8.3.8 ; ; ; Selected TOC Entries: ; 3; 2615 2200 SCHEMA - public pasha 1861; 0 0 COMMENT - SCHEMA public pasha 1862; 0 0 ACL - public pasha 317; 1247 17715 TYPE public composite pasha 319; 1247 25899 DOMAIN public domain0 pasha ``` -------------------------------- ### Configure the build directory with meson setup Source: https://www.postgresql.org/docs/current/install-meson.html Creates and configures the build directory. The builddir argument is mandatory; if no srcdir is given, Meson deduces it from the current directory and the location of meson.build. ```shell meson setup build ``` -------------------------------- ### Start upgraded publisher server on node1 Source: https://www.postgresql.org/docs/current/logical-replication-upgrade.html Start the upgraded publisher server in node1 using pg_ctl with the upgraded data directory /opt/PostgreSQL/data1_upgraded and log to logfile. ```bash pg_ctl -D /opt/PostgreSQL/data1_upgraded start -l logfile ``` -------------------------------- ### Create publication for examples Source: https://www.postgresql.org/docs/current/logical-replication-subscription.html Creates a publication named pub1 for all tables, used as the basis for the deferred replication slot examples. ```sql /* pub # */ CREATE PUBLICATION pub1 FOR ALL TABLES; ``` -------------------------------- ### Start the upgraded node3 server with pg_ctl Source: https://www.postgresql.org/docs/current/logical-replication-upgrade.html Start the upgraded server on node3 using the upgraded data directory and write output to a logfile. ```bash pg_ctl -D /opt/PostgreSQL/data3_upgraded start -l logfile ``` -------------------------------- ### Build everything including documentation and contrib with make world Source: https://www.postgresql.org/docs/current/install-make.html Build all components: server, utilities, documentation (HTML and man pages), and additional modules (contrib). ```shell make world ``` -------------------------------- ### Create publication mypub on the publisher database Source: https://www.postgresql.org/docs/current/logical-replication-quick-setup.html Run on the publisher database to publish the users and departments tables. ```sql CREATE PUBLICATION mypub FOR TABLE users, departments; ``` -------------------------------- ### subarray(integer[], start integer) → integer[] Source: https://www.postgresql.org/docs/current/intarray.html Extracts the portion of the array starting at position start. Example: subarray('{1,2,3,2,1}'::integer[], 2) → {2,3,2,1}. ```APIDOC ## subarray(integer[], start integer) → integer[] ### Description Extracts the portion of the array starting at position `start`. ### Signature `subarray(integer[], start integer) → integer[]` ### Parameters - **integer[]** (integer[]) - Required - The input array. - **start** (integer) - Required - The starting position. ### Returns - **integer[]** - The extracted subarray. ### Example `subarray('{1,2,3,2,1}'::integer[], 2)` → `{2,3,2,1}` ``` -------------------------------- ### subarray(integer[], start integer, len integer) → integer[] Source: https://www.postgresql.org/docs/current/intarray.html Extracts the portion of the array starting at position start, with len elements. Example: subarray('{1,2,3,2,1}'::integer[], 2, 3) → {2,3,2}. ```APIDOC ## subarray(integer[], start integer, len integer) → integer[] ### Description Extracts the portion of the array starting at position `start`, with `len` elements. ### Signature `subarray(integer[], start integer, len integer) → integer[]` ### Parameters - **integer[]** (integer[]) - Required - The input array. - **start** (integer) - Required - The starting position. - **len** (integer) - Required - The number of elements to extract. ### Returns - **integer[]** - The extracted subarray. ### Example `subarray('{1,2,3,2,1}'::integer[], 2, 3)` → `{2,3,2}` ``` -------------------------------- ### Build tutorial scripts and C files with make Source: https://www.postgresql.org/docs/current/tutorial-sql-intro.html Run from the PostgreSQL source distribution's src/tutorial/ directory to create the tutorial scripts and compile the C files containing user-defined functions and types. Binary distributions might not provide these files. ```shell $ cd .../src/tutorial $ make ``` -------------------------------- ### Extract bits with substring Source: https://www.postgresql.org/docs/current/functions-bitstring.html substring extracts bits starting at start and stopping after count bits; provide at least one of start and count. Example shows substring(B'110010111111' from 3 for 2) producing 00. ```sql substring(B'110010111111' from 3 for 2)` → `00 ``` -------------------------------- ### Create ascending sequence serial starting at 101 Source: https://www.postgresql.org/docs/current/sql-createsequence.html Creates a sequence named `serial` that starts at 101 and ascends. Use this as the base sequence for the following nextval examples. ```sql CREATE SEQUENCE serial START 101; ``` -------------------------------- ### Run ldconfig on Linux with root access Source: https://www.postgresql.org/docs/current/install-post.html Enables the run-time linker to find the shared libraries faster after installation. Use the equivalent directory if you did not install into /usr/local/pgsql/lib. ```shell /sbin/ldconfig /usr/local/pgsql/lib ``` -------------------------------- ### Create database demo with default server using createdb Source: https://www.postgresql.org/docs/current/app-createdb.html Run createdb with a database name to create it on the default database server. ```shell $ createdb demo ``` -------------------------------- ### Start the tutorial in psql with single-step mode and load basics.sql Source: https://www.postgresql.org/docs/current/tutorial-sql-intro.html Starts psql against the mydb database in single-step mode (-s), which pauses before sending each statement to the server. The \i command then reads in commands from the specified file, basics.sql. ```shell $ psql -s mydb ... mydb=> \i basics.sql ``` -------------------------------- ### Manual installation of PL/Perl: declare inline handler and validator Source: https://www.postgresql.org/docs/current/xplang-install.html Second part of Example 40.1: PL/Perl has an inline handler and a validator function, so both are declared. ```sql CREATE FUNCTION plperl_inline_handler(internal) RETURNS void AS '$libdir/plperl' LANGUAGE C STRICT; CREATE FUNCTION plperl_validator(oid) RETURNS void AS '$libdir/plperl' LANGUAGE C STRICT; ``` -------------------------------- ### Expected output of the GET DESCRIPTOR example Source: https://www.postgresql.org/docs/current/ecpg-sql-get-descriptor.html Output produced when the full procedure is executed: one column, length 6, and data testdb. ```text d_count = 1 d_returned_octet_length = 6 d_data = testdb ``` -------------------------------- ### Build everything except documentation with make world-bin Source: https://www.postgresql.org/docs/current/install-make.html Build all components including additional modules (contrib) but without documentation. ```shell make world-bin ``` -------------------------------- ### Run libpq Example Program 1 (testlibpq.c) Source: https://www.postgresql.org/docs/current/libpq-example.html Full C example using libpq to connect, set a secure search_path, and fetch rows from pg_database via a cursor inside a transaction. Pass a conninfo string as argv[1], or it defaults to dbname = postgres. Source file is src/test/examples/testlibpq.c. ```c /* * src/test/examples/testlibpq.c * * * testlibpq.c * * Test the C version of libpq, the PostgreSQL frontend library. */ #include #include #include "libpq-fe.h" static void exit_nicely(PGconn *conn) { PQfinish(conn); exit(1); } int main(int argc, char **argv) { const char *conninfo; PGconn *conn; PGresult *res; int nFields; int i, j; /* * If the user supplies a parameter on the command line, use it as the * conninfo string; otherwise default to setting dbname=postgres and using * environment variables or defaults for all other connection parameters. */ if (argc > 1) conninfo = argv[1]; else conninfo = "dbname = postgres"; /* Make a connection to the database */ conn = PQconnectdb(conninfo); /* Check to see that the backend connection was successfully made */ if (PQstatus(conn) != CONNECTION_OK) { fprintf(stderr, "%s", PQerrorMessage(conn)); exit_nicely(conn); } /* Set always-secure search path, so malicious users can't take control. */ res = PQexec(conn, "SELECT pg_catalog.set_config('search_path', '', false)"); if (PQresultStatus(res) != PGRES_TUPLES_OK) { fprintf(stderr, "SET failed: %s", PQerrorMessage(conn)); PQclear(res); exit_nicely(conn); } /* * Should PQclear PGresult whenever it is no longer needed to avoid memory * leaks */ PQclear(res); /* * Our test case here involves using a cursor, for which we must be inside * a transaction block. We could do the whole thing with a single * PQexec() of "select * from pg_database", but that's too trivial to make * a good example. */ /* Start a transaction block */ res = PQexec(conn, "BEGIN"); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn)); PQclear(res); exit_nicely(conn); } PQclear(res); /* * Fetch rows from pg_database, the system catalog of databases */ res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database"); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn)); PQclear(res); exit_nicely(conn); } PQclear(res); res = PQexec(conn, "FETCH ALL in myportal"); if (PQresultStatus(res) != PGRES_TUPLES_OK) { fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn)); PQclear(res); exit_nicely(conn); } /* first, print out the attribute names */ nFields = PQnfields(res); for (i = 0; i < nFields; i++) printf("%-15s", PQfname(res, i)); printf("\n\n"); /* next, print out the rows */ for (i = 0; i < PQntuples(res); i++) { for (j = 0; j < nFields; j++) printf("%-15s", PQgetvalue(res, i, j)); printf("\n"); } PQclear(res); /* close the portal ... we don't bother to check for errors ... */ res = PQexec(conn, "CLOSE myportal"); PQclear(res); /* end the transaction */ res = PQexec(conn, "END"); PQclear(res); /* close the connection to the database and cleanup */ PQfinish(conn); return 0; } ``` -------------------------------- ### Install hstore extension into addons schema Source: https://www.postgresql.org/docs/current/sql-createextension.html Installs the hstore extension into the current database with its objects placed in the addons schema. ```sql CREATE EXTENSION hstore SCHEMA addons; ``` -------------------------------- ### OVERLAPS operator examples Source: https://www.postgresql.org/docs/current/functions-datetime.html Each time period is a half-open interval start <= time < end unless start equals end, in which case it is a single instant. Periods sharing only an endpoint do not overlap. ```sql SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS (DATE '2001-10-30', DATE '2002-10-30'); Result: true SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS (DATE '2001-10-30', DATE '2002-10-30'); Result: false SELECT (DATE '2001-10-29', DATE '2001-10-30') OVERLAPS (DATE '2001-10-30', DATE '2001-10-31'); Result: false SELECT (DATE '2001-10-30', DATE '2001-10-30') OVERLAPS (DATE '2001-10-30', DATE '2001-10-31'); Result: true ``` -------------------------------- ### Start a base backup with pg_backup_start Source: https://www.postgresql.org/docs/current/continuous-archiving.html Run this SQL command on a connection with rights to execute pg_backup_start (superuser or granted EXECUTE). The connection must remain open until the backup ends, or the backup is automatically aborted. Passing fast => true requests an immediate checkpoint instead of waiting for the next scheduled one. ```sql SELECT pg_backup_start(label => 'label', fast => false); ``` -------------------------------- ### Manual installation of PL/Perl: declare the call handler Source: https://www.postgresql.org/docs/current/xplang-install.html First command of Example 40.1: tells the database server where to find the shared object for PL/Perl's call handler function. ```sql CREATE FUNCTION plperl_call_handler() RETURNS language_handler AS '$libdir/plperl' LANGUAGE C; ``` -------------------------------- ### Manual installation of PL/Perl: create the trusted language Source: https://www.postgresql.org/docs/current/xplang-install.html Final command of Example 40.1: declares PL/Perl as a trusted language using the previously created handler, inline handler, and validator functions. ```sql CREATE TRUSTED LANGUAGE plperl HANDLER plperl_call_handler INLINE plperl_inline_handler VALIDATOR plperl_validator; ``` -------------------------------- ### psql startup greeting and prompt Source: https://www.postgresql.org/docs/current/tutorial-accessdb.html After connecting, psql prints its version and a help hint, followed by the mydb=> prompt indicating it is ready for SQL input. ```text psql (18.6) Type "help" for help. mydb=> ``` -------------------------------- ### now current transaction timestamp Source: https://www.postgresql.org/docs/current/functions-datetime.html Returns the current date and time at the start of the current transaction. Example output: 2019-12-23 14:39:53.662522-05. ```sql now() → `2019-12-23 14:39:53.662522-05` ``` -------------------------------- ### Start upgraded subscriber server on node2 Source: https://www.postgresql.org/docs/current/logical-replication-upgrade.html Start the upgraded subscriber server in node2 using pg_ctl with the upgraded data directory /opt/PostgreSQL/data2_upgraded and log to logfile. ```bash pg_ctl -D /opt/PostgreSQL/data2_upgraded start -l logfile ``` -------------------------------- ### Out-of-line Parameters and Binary I/O Example (testlibpq3.c) Source: https://www.postgresql.org/docs/current/libpq-example.html Tests out-of-line parameters and binary I/O. Before running, populate the database using the provided SQL commands. Expected output shows tuples with binary data. ```c /* * src/test/examples/testlibpq3.c * * * testlibpq3.c * Test out-of-line parameters and binary I/O. * * Before running this, populate a database with the following commands * (provided in src/test/examples/testlibpq3.sql): * * CREATE SCHEMA testlibpq3; * SET search_path = testlibpq3; * SET standard_conforming_strings = ON; * CREATE TABLE test1 (i int4, t text, b bytea); * INSERT INTO test1 values (1, 'joe''s place', '\000\001\002\003\004'); * INSERT INTO test1 values (2, 'ho there', '\004\003\002\001\000'); * * The expected output is: * * tuple 0: got * i = (4 bytes) 1 * t = (11 bytes) 'joe's place' * b = (5 bytes) \000\001\002\003\004 * * tuple 0: got * i = (4 bytes) 2 * t = (8 bytes) 'ho there' * b = (5 bytes) \004\003\002\001\000 */ #ifdef WIN32 #include #endif #include #include #include #include #include #include "libpq-fe.h" /* for ntohl/htonl */ #include #include ``` -------------------------------- ### Compute selectivity within MCV population Source: https://www.postgresql.org/docs/current/row-estimation-examples.html Sums the frequencies of the MCVs that satisfy the condition to get the selectivity within the MCV portion. ```text selectivity = sum(relevant mvfs) = 0.00333333 + 0.003 + 0.003 + 0.003 + 0.003 + 0.003 = 0.01833333 ``` -------------------------------- ### Create tables t1, t2, t3 for row filter examples Source: https://www.postgresql.org/docs/current/logical-replication-row-filter.html Creates the base tables used by the publication and subscription examples that follow. ```sql /* pub # */ CREATE TABLE t1(a int, b int, c text, PRIMARY KEY(a,c)); /* pub # */ CREATE TABLE t2(d int, e int, f int, PRIMARY KEY(d)); /* pub # */ CREATE TABLE t3(g int, h int, i int, PRIMARY KEY(g)); ``` -------------------------------- ### Back up database installation with pg_dumpall Source: https://www.postgresql.org/docs/current/upgrading.html Dumps the entire database installation to a file. Ensure the database is not being updated during the backup; changed data would not be included. ```shell pg_dumpall > outputfile ``` -------------------------------- ### transaction_timestamp current transaction time Source: https://www.postgresql.org/docs/current/functions-datetime.html Returns the current date and time at the start of the current transaction. Example output: 2019-12-23 14:39:53.662522-05. ```sql transaction_timestamp() → `2019-12-23 14:39:53.662522-05` ``` -------------------------------- ### statement_timestamp current statement time Source: https://www.postgresql.org/docs/current/functions-datetime.html Returns the current date and time at the start of the current statement. Example output: 2019-12-23 14:39:53.662522-05. ```sql statement_timestamp() → `2019-12-23 14:39:53.662522-05` ``` -------------------------------- ### Show all settings with SHOW ALL Source: https://www.postgresql.org/docs/current/sql-show.html Lists every configuration parameter with its setting and description; output is truncated here with ellipses. ```sql SHOW ALL; name | setting | description -------------------------+---------+------------------------------------------------- allow_system_table_mods | off | Allows modifications of the structure of ... . . . xmloption | content | Sets whether XML data in implicit parsing ... zero_damaged_pages | off | Continues processing past damaged page headers. (196 rows) ``` -------------------------------- ### localtimestamp current date and time Source: https://www.postgresql.org/docs/current/functions-datetime.html Returns the current date and time at the start of the current transaction. Example output: 2019-12-23 14:39:53.662522. ```sql localtimestamp → `2019-12-23 14:39:53.662522` ``` -------------------------------- ### Initialize a fresh cluster with sepgsql functions and security labels Source: https://www.postgresql.org/docs/current/sepgsql.html Sets PGDATA, runs initdb, adds sepgsql to shared_preload_libraries in postgresql.conf, then loads sepgsql.sql into template0, template1, and postgres. Adjust the paths for your installation. ```shell $ export PGDATA=/path/to/data/directory $ initdb $ vi $PGDATA/postgresql.conf change #shared_preload_libraries = '' # (change requires restart) to shared_preload_libraries = 'sepgsql' # (change requires restart) $ for DBNAME in template0 template1 postgres; do postgres --single -F -c exit_on_error=true $DBNAME \ /dev/null done ``` -------------------------------- ### GET DIAGNOSTICS ROW_COUNT example Source: https://www.postgresql.org/docs/current/plpgsql-statements.html Assigns the number of rows processed by the most recent SQL command to integer_var. ROW_COUNT is of type bigint. ```sql GET DIAGNOSTICS integer_var = ROW_COUNT; ``` -------------------------------- ### libpq binary results example with PQexecParams and show_binary_results Source: https://www.postgresql.org/docs/current/libpq-example.html Illustrates PQexecParams with out-of-line text parameters and binary result retrieval, plus a helper that decodes INT4, TEXT, and BYTEA binary fields. Requires a testlibpq3 schema with a test1 table; the search path is set to testlibpq3 for safety. ```c static void exit_nicely(PGconn *conn) { PQfinish(conn); exit(1); } /* * This function prints a query result that is a binary-format fetch from * a table defined as in the comment above. We split it out because the * main() function uses it twice. */ static void show_binary_results(PGresult *res) { int i, j; int i_fnum, t_fnum, b_fnum; /* Use PQfnumber to avoid assumptions about field order in result */ i_fnum = PQfnumber(res, "i"); t_fnum = PQfnumber(res, "t"); b_fnum = PQfnumber(res, "b"); for (i = 0; i < PQntuples(res); i++) { char *iptr; char *tptr; char *bptr; int blen; int ival; /* Get the field values (we ignore possibility they are null!) */ iptr = PQgetvalue(res, i, i_fnum); tptr = PQgetvalue(res, i, t_fnum); bptr = PQgetvalue(res, i, b_fnum); /* * The binary representation of INT4 is in network byte order, which * we'd better coerce to the local byte order. */ ival = ntohl(*((uint32_t *) iptr)); /* * The binary representation of TEXT is, well, text, and since libpq * was nice enough to append a zero byte to it, it'll work just fine * as a C string. * * The binary representation of BYTEA is a bunch of bytes, which could * include embedded nulls so we have to pay attention to field length. */ blen = PQgetlength(res, i, b_fnum); printf("tuple %d: got\n", i); printf(" i = (%d bytes) %d\n", PQgetlength(res, i, i_fnum), ival); printf(" t = (%d bytes) '%s'\n", PQgetlength(res, i, t_fnum), tptr); printf(" b = (%d bytes) ", blen); for (j = 0; j < blen; j++) printf("\\%03o", bptr[j]); printf("\n\n"); } } int main(int argc, char **argv) { const char *conninfo; PGconn *conn; PGresult *res; const char *paramValues[1]; int paramLengths[1]; int paramFormats[1]; uint32_t binaryIntVal; /* * If the user supplies a parameter on the command line, use it as the * conninfo string; otherwise default to setting dbname=postgres and using * environment variables or defaults for all other connection parameters. */ if (argc > 1) conninfo = argv[1]; else conninfo = "dbname = postgres"; /* Make a connection to the database */ conn = PQconnectdb(conninfo); /* Check to see that the backend connection was successfully made */ if (PQstatus(conn) != CONNECTION_OK) { fprintf(stderr, "%s", PQerrorMessage(conn)); exit_nicely(conn); } /* Set always-secure search path, so malicious users can't take control. */ res = PQexec(conn, "SET search_path = testlibpq3"); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "SET failed: %s", PQerrorMessage(conn)); PQclear(res); exit_nicely(conn); } PQclear(res); /* * The point of this program is to illustrate use of PQexecParams() with * out-of-line parameters, as well as binary transmission of data. * * This first example transmits the parameters as text, but receives the * results in binary format. By using out-of-line parameters we can avoid * a lot of tedious mucking about with quoting and escaping, even though * the data is text. Notice how we don't have to do anything special with * the quote mark in the parameter value. */ /* Here is our out-of-line parameter value */ paramValues[0] = "joe's place"; res = PQexecParams(conn, "SELECT * FROM test1 WHERE t = $1", 1, /* one param */ NULL, /* let the backend deduce param type */ paramValues, NULL, /* don't need param lengths since text */ NULL, /* default to all text params */ 1); /* ask for binary results */ if (PQresultStatus(res) != PGRES_TUPLES_OK) { fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn)); PQclear(res); exit_nicely(conn); } show_binary_results(res); PQclear(res); /* * In this second example we transmit an integer parameter in binary form, * and again retrieve the results in binary form. * * Although we tell PQexecParams we are letting the backend deduce * parameter type, we really force the decision by casting the parameter * symbol in the query text. This is a good safety measure when sending * binary parameters. */ /* Convert integer value "2" to network byte order */ binaryIntVal = htonl((uint32_t) 2); ``` -------------------------------- ### createdb connection to server failed error Source: https://www.postgresql.org/docs/current/tutorial-createdb.html This error means the server was not started or is not listening where `createdb` expects to contact it. Check the installation instructions or consult the administrator. ```text createdb: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory Is the server running locally and accepting connections on that socket? ``` -------------------------------- ### Initialize a new message catalog with make init-po Source: https://www.postgresql.org/docs/current/nls-translator.html Run this command in the directory containing the program to create a .pot template file. Copy the resulting progname.pot to language.po and edit it. ```shell make init-po ``` -------------------------------- ### Get byte length with octet_length Source: https://www.postgresql.org/docs/current/functions-bitstring.html octet_length returns the number of bytes in the bit string. Example shows octet_length(B'1011111011') returning 2. ```sql octet_length(B'1011111011')` → `2 ``` -------------------------------- ### Get bit length with length Source: https://www.postgresql.org/docs/current/functions-bitstring.html length returns the number of bits in the bit string. Example shows length(B'10111') returning 5. ```sql length(B'10111')` → `5 ``` -------------------------------- ### Create synonym dictionary pg_dict Source: https://www.postgresql.org/docs/current/textsearch-configuration.html Defines the synonym dictionary pg_dict using the synonym template and the pg_dict synonym file. ```sql CREATE TEXT SEARCH DICTIONARY pg_dict ( TEMPLATE = synonym, SYNONYMS = pg_dict ); ``` -------------------------------- ### Get bit length with bit_length Source: https://www.postgresql.org/docs/current/functions-bitstring.html bit_length returns the number of bits in the bit string. Example shows bit_length(B'10111') returning 5. ```sql bit_length(B'10111')` → `5 ``` -------------------------------- ### Create table t3 with integer array column Source: https://www.postgresql.org/docs/current/ecpg-variables.html Setup table used by the array element retrieval examples; column ii is an integer array. ```sql CREATE TABLE t3 ( ii integer[] ); testdb=> SELECT * FROM t3; ii ------------- {1,2,3,4,5} (1 row) ``` -------------------------------- ### Attach a new table as a partition using CREATE TABLE ... LIKE and ATTACH PARTITION Source: https://www.postgresql.org/docs/current/ddl-partitioning.html Create a standalone table with CREATE TABLE ... LIKE, load and prepare data, then attach it as a partition. Adding a matching CHECK constraint before attaching avoids the ACCESS EXCLUSIVE scan of the table during ATTACH PARTITION. ```sql CREATE TABLE measurement_y2008m02 (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS) TABLESPACE fasttablespace; ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02 CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' ); \copy measurement_y2008m02 from 'measurement_y2008m02' -- possibly some other data preparation work ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02 FOR VALUES FROM ('2008-02-01') TO ('2008-03-01' ); ``` -------------------------------- ### Using GET STACKED DIAGNOSTICS to retrieve error details Source: https://www.postgresql.org/docs/current/plpgsql-control-structures.html Example of retrieving MESSAGE_TEXT, PG_EXCEPTION_DETAIL, and PG_EXCEPTION_HINT into variables within an exception handler. If an item is not set, an empty string is returned. ```sql DECLARE text_var1 text; text_var2 text; text_var3 text; BEGIN -- some processing which might cause an exception ... EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT, text_var2 = PG_EXCEPTION_DETAIL, text_var3 = PG_EXCEPTION_HINT; END; ``` -------------------------------- ### Show plan with JSON output using EXPLAIN (FORMAT JSON) Source: https://www.postgresql.org/docs/current/sql-explain.html Same query as the simple sequential scan example, but rendered as JSON output. ```sql EXPLAIN (FORMAT JSON) SELECT * FROM foo; QUERY PLAN -------------------------------- [ + { + "Plan": { + "Node Type": "Seq Scan",+ "Relation Name": "foo", + "Alias": "foo", + "Startup Cost": 0.00, + "Total Cost": 155.00, + "Plan Rows": 10000, + "Plan Width": 4 + } + } + ] (1 row) ``` -------------------------------- ### localtimestamp with limited precision Source: https://www.postgresql.org/docs/current/functions-datetime.html Returns the current date and time at the start of the current transaction with limited precision. Example localtimestamp(2) returns 2019-12-23 14:39:53.66. ```sql localtimestamp(2) → `2019-12-23 14:39:53.66` ``` -------------------------------- ### Reconfigure the build with meson configure Source: https://www.postgresql.org/docs/current/install-meson.html Setting up the build directory is a one-time step; use meson configure to change options before a new build. ```shell meson configure -Dcassert=true ``` -------------------------------- ### Find header flags with pkg-config --cflags libpq Source: https://www.postgresql.org/docs/current/libpq-build.html If pkg-config is installed, use it to get the compiler flags for libpq. The output already includes the -I prefix before the path. ```shell $ pkg-config --cflags libpq -I/usr/local/include ``` -------------------------------- ### Run ldconfig on FreeBSD, NetBSD, and OpenBSD Source: https://www.postgresql.org/docs/current/install-post.html The ldconfig command variant for BSD systems, using the -m flag. Other systems are not known to have an equivalent command. ```shell /sbin/ldconfig -m /usr/local/pgsql/lib ``` -------------------------------- ### Output of the txn_count.d DTrace script Source: https://www.postgresql.org/docs/current/dynamic-trace.html Example output produced when running the transaction-count D script, invoked as ./txn_count.d `pgrep -n postgres` or ./txn_count.d . Shows Start, Commit, and Total time (ns) aggregates. ```text # ./txn_count.d `pgrep -n postgres` or ./txn_count.d ^C Start 71 Commit 70 Total time (ns) 2312105013 ``` -------------------------------- ### Example postgresql.conf configuration file Source: https://www.postgresql.org/docs/current/config-setting.html Shows the format of postgresql.conf: one parameter per line, optional equal sign, insignificant whitespace, and `#` comments. Values that are not simple identifiers or numbers must be single-quoted. ```ini # This is a comment log_connections = all log_destination = 'syslog' search_path = '"$user", public' shared_buffers = 128MB ``` -------------------------------- ### Retrieve comment for a database object using obj_description with catalog Source: https://www.postgresql.org/docs/current/functions-info.html Use obj_description with the object OID and catalog name to get a comment. Example: obj_description(123456, 'pg_class') retrieves the comment for the table with OID 123456. ```sql obj_description ( object oid, catalog name ) → text ``` -------------------------------- ### Install hstore extension using search_path Source: https://www.postgresql.org/docs/current/sql-createextension.html Alternative to specifying SCHEMA: set search_path to addons, then create the hstore extension so its objects go there. ```sql SET search_path = addons; CREATE EXTENSION hstore; ```