Useful PostgreSQL Commands¶
PG example user
Many of these examples use a role (user) called devel.
Miscellaneous Commands¶
Get PostgresSQL version:
SELECT version();
Or, from a running container:
$ docker exec pgsql1 bash -c "psql -U devel <<<'SELECT VERSION()'"
version
-----------------------------------------------------------------------
PostgreSQL 14.8 (Debian 14.8-1.pgdg120+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
We say docker exec
followed by the name of the container.
Then, on that container, run bash -c
to run a bash command, and the command is the psql command we want.
In this case, drop into psql with the user devel
and provide it with the SELECT VERSION()
SQL command through a bash here string.
Get type of a value:
SELECT pg_typeof(1);
pg_typeof | integer
SELECT pg_typeof(1) as type_of_1;
type_of_1 | integer
SELECT pg_typeof(1::NUMERIC);
pg_typeof | numeric
blog1_dev=# SELECT pg_typeof('xyz');
pg_typeof | unknown
blog1_dev=# SELECT pg_typeof('xyz'::VARCHAR(8));
pg_typeof | character varying
blog1_dev=# SELECT pg_typeof('xyz'::TEXT);
pg_typeof | text
List pg types:
SELECT oid, typname, typlen FROM pg_type;
See:
Roles (users)¶
Create role:
CREATE ROLE devel
WITH LOGIN PASSWORD 's3cr37' CREATEDB REPLICATION
VALID UNTIL 'infinity';
Creating Databases¶
Create database:
CREATE DATABASE devel WITH
ENCODING='UTF-8'
OWNER=devel
LC_CTYPE='en_US.UTF-8'
LC_COLLATE='en_US.UTF-8'
TEMPLATE=template0
CONNECTION LIMIT=3;
Make role devel
as powerful as postgres role:
ALTER USER devel WITH SUPERUSER;
Reverse the effects of the command above:
ALTER USER devel WITH NOSUPERUSER;
Backup & Restore¶
Intro Notes¶
pg_dump
can dump data in a few different formats.
SQL (plain text) dump output must be fed back into psql
.
The “other formats” should be fed to pg_restore
.
Also:
It is not guaranteed that
pg_dump
’s output can be loaded into a server of an older major version — not even if the dump was taken from a server of that version. Loading a dump file into an older server may require manual editing of the dump file to remove syntax not understood by the older server. – pg_dump v15 docs
From local database¶
Dump a local database in plain text (SQL) format:
$ pg_dump -U devel -W -C -Fp mydb -f mydb.sql
Import a plain text (SQL) dump:
psql -U devel -d mydb -f _stuff/mydb.sql
Restore a Heroku Postgres dump:
$ pg_restore -U devel -d mydb mydb.backup
From Docker Container¶
One approach is to use pg_dump
from the host machine, if available.
$ pg_dump -h <host> -U <user> -C -Fp mydb --file ./mydb.sql
But if pg_dump
version is older than the one on the server, it will refuse to try to dump the data from a newer version, or some other incompatibilities (see pg_dump docs).
If it is newer, it could introduce syntax and options that would then fail to be fed back to a server running an older version, thus requiring manual fixes on the dumped SQL.
Another approach is to use docker exec
and dump the database using the container’s version of pg_dump
.
This way, pg_dump
does NOT need to be installed on the host machine and it will certainly match the version of the PostgreSQL server running on the container, reducing the likelihood of problems and incompatibilities.
$ docker exec postgresql14-playground-1 pg_dump --version
pg_dump (PostgreSQL) 14.8 (Debian 14.8-1.pgdg120+1)
So, we can do something like this:
$ docker exec pgsql-container \
pg_dump -U devel -C -Fp mydb \
| tee ./mydb.sql
We could also replace the pipe and tee
with a redirection:
$ docker exec pgsql-container \
pg_dump -U devel -C -Fp mydb \
> ./mydb.sql
Note
DO NOT use the -it
docker exec
options.
Those are used if you want an interactive terminal/shell session with the running container, but here, we just want a non-interactive terminal to dump the database and pipe it to a file on the host machine.
If you use them (specifically -t
), it will insert pseudo tty bytes into the output, potentially corrupting it.
I observed, for example, that it was causing CRLF line terminators being inserted into the dumped .sql
files.
Because Docker runs Linux, and my local desktop was Arch Linux, that was not expected.
PostgreSQL inside the container should be using standard Linux newlines (\n
, or linefeed, or 0x0a) for line terminators, not \r\n
Windows line terminators.
Anyways, removing -it
from the command for dumping and piping data “fixed” the issue.
Renaming a Database¶
Make sure no user or application is connected to the database otherwise this command will not work:
devel=# ALTER DATABASE blog RENAME TO blog_dev;
ERROR: database "blog" is being accessed by other users
DETAIL: There is 1 other session using the database.
After no sessions are active, it should work:
devel=# ALTER DATABASE blog RENAME TO blog_dev;
ALTER DATABASE
Note the output is simply “ALTER DATABASE”, which means the command ran successfully.
References:
Tables¶
PostgreSQL has \d
and \dt
to inspect a table.
Those are not standard SQL, but PostgreSQL specific features.
We can also use standard SQL to inspect a table (should work across many different database vendors):
SELECT
table_name
, column_name
, data_type
FROM
information_schema.columns
WHERE
table_name = 'users';
There is an overwhelming number of columns to display. See it for yourself:
SELECT *
FROM information_schema.columns
WHERE table_name = 'users';