postgresql.1

.Dd April 1, 2025
.Dt PLAKAR-POSTGRESQL 1
.Os
.Sh NAME
.Nm plakar-postgresql
.Nd PostgreSQL backup and restoration connector for Plakar
.Sh DESCRIPTION
The
.Nm
integration package provides two independent backup strategies for PostgreSQL.
.Bl -tag -width "postgres+bin://"
.It Li postgres://
Logical backup and restore using
.Xr pg_dump 1 ,
.Xr pg_dumpall 1 ,
.Xr pg_restore 1 ,
and
.Xr psql 1 .
Produces portable, version-independent SQL dumps.
.It Li postgres+bin://
Physical backup using
.Xr pg_basebackup 1 .
Streams a binary copy of the entire PostgreSQL data directory.
Restore is performed by any file-restore connector.
.El
.Sh LOGICAL BACKUP
.Ss How it works
When a
.Ar database
is specified the source connector runs
.Xr pg_dump 1
.Fl F Ns Li c
to produce a custom-format dump
.Pq Li <dbname>.dump
and
.Xr pg_dumpall 1
.Fl -globals-only
to capture roles and tablespaces
.Pq Li globals.sql .
.Pp
When no database is specified the source connector runs
.Xr pg_dumpall 1
to produce a plain-SQL dump of every database on the server
.Pq Li all.sql ,
which already includes global objects.
.Pp
The destination connector dispatches on filename:
.Xr pg_restore 1
is used for
.Li .dump
files;
.Xr psql 1
is used for
.Li .sql
files.
.Ss Prerequisites
The following programs must be present in
.Ev PATH
on the machine running Plakar:
.Pp
.Bl -tag -width "pg_dumpall" -compact
.It pg_dump
Single-database backups.
.It pg_dumpall
Full-server backups and globals-only dumps.
.It pg_restore
Restoring single-database dumps.
.It psql
Restoring full-server dumps and connectivity checks.
.El
.Pp
The backup user should be a superuser so that
.Xr pg_dumpall 1
can read
.Li pg_authid
and include role passwords.
On managed services such as Amazon RDS the administrative user is a
restricted superuser that cannot read
.Li pg_authid .
The importer detects this automatically and falls back to
.Fl -no-role-passwords ,
and logs a warning.
The dump is otherwise complete, but restored roles will have no password set.
.Ss Source options
.Bl -tag -width "schema_only"
.It Ar location
Connection URI:
.Li postgres://[user[:password]@]host[:port][/database] .
.It Ar host
Server hostname (default:
.Li localhost ) .
Overrides the URI host.
.It Ar port
Server port (default:
.Li 5432 ) .
Overrides the URI port.
.It Ar username
Authentication username.
Overrides the URI user.
.It Ar password
Authentication password, passed via
.Ev PGPASSWORD .
Overrides the URI password.
.It Ar database
Database to back up.
If omitted, all databases are backed up via
.Xr pg_dumpall 1 .
Overrides the URI path.
.It Ar compress
Enable
.Xr pg_dump 1
compression (default:
.Li false ) .
Disabled by default so that Plakar's own compression operates on
uncompressed data.
.It Ar schema_only
Dump only the schema, no data (default:
.Li false ) .
Mutually exclusive with
.Ar data_only .
.It Ar data_only
Dump only the data, no schema (default:
.Li false ) .
Mutually exclusive with
.Ar schema_only .
.It Ar pg_bin_dir
Directory containing the PostgreSQL client binaries
.Pq pg_dump , pg_dumpall , psql .
When omitted, binaries are resolved via
.Ev PATH .
Useful when multiple PostgreSQL versions are installed.
.It Ar ssl_mode
SSL mode for the connection (default:
.Li prefer ) .
Accepted values:
.Li disable ,
.Li allow ,
.Li prefer ,
.Li require ,
.Li verify-ca ,
.Li verify-full .
Passed via
.Ev PGSSLMODE .
.It Ar ssl_cert
Path to the client SSL certificate file (PEM format).
Passed via
.Ev PGSSLCERT .
.It Ar ssl_cert_data
Inline PEM content of the client SSL certificate.
Alternative to
.Ar ssl_cert .
.It Ar ssl_key
Path to the client SSL private key file (PEM format).
Passed via
.Ev PGSSLKEY .
.It Ar ssl_key_data
Inline PEM content of the client SSL private key.
Alternative to
.Ar ssl_key .
.It Ar ssl_root_cert
Path to the root CA certificate used to verify the server (PEM format).
Passed via
.Ev PGSSLROOTCERT .
.It Ar ssl_root_cert_data
Inline PEM content of the root CA certificate.
Alternative to
.Ar ssl_root_cert .
.El
.Ss Destination options
.Bl -tag -width "no_globals"
.It Ar location
Connection URI:
.Li postgres://[user[:password]@]host[:port][/database] .
.It Ar host
Server hostname (default:
.Li localhost ) .
Overrides the URI host.
.It Ar port
Server port (default:
.Li 5432 ) .
Overrides the URI port.
.It Ar username
Authentication username.
Overrides the URI user.
.It Ar password
Authentication password, passed via
.Ev PGPASSWORD .
Overrides the URI password.
.It Ar database
Target database for
.Xr pg_restore 1 .
If omitted, the name is inferred from the dump filename.
See
.Ar create_db
for how this interacts with database creation.
.It Ar create_db
When
.Li false
(default),
.Fl d
names the target database, which must already exist.
When
.Li true ,
passes
.Fl C
to
.Xr pg_restore 1
so the database is created from the archive metadata;
.Fl d
then names only the initial connection database (defaults to
.Li postgres
if unset).
.It Ar no_globals
When
.Li true ,
skips feeding the globals file
.Li ( globals.sql
or
.Li 00000-globals.sql )
to
.Xr psql 1 .
By default globals are restored automatically when present (default:
.Li false ) .
.It Ar no_owner
Pass
.Fl -no-owner
to
.Xr pg_restore 1 ,
skipping
.Li ALTER OWNER
statements (default:
.Li false ) .
.It Ar exit_on_error
Stop on the first restore error (default:
.Li false ) .
Passes
.Fl e
to
.Xr pg_restore 1
and
.Li ON_ERROR_STOP=1
to
.Xr psql 1 .
.It Ar schema_only
Restore only the schema, no data (default:
.Li false ) .
Mutually exclusive with
.Ar data_only .
Not applicable to
.Xr pg_dumpall 1
restores.
.It Ar data_only
Restore only the data, no schema (default:
.Li false ) .
Mutually exclusive with
.Ar schema_only .
Not applicable to
.Xr pg_dumpall 1
restores.
.It Ar pg_bin_dir
Directory containing the PostgreSQL client binaries
.Pq pg_restore , psql .
When omitted, binaries are resolved via
.Ev PATH .
Useful when multiple PostgreSQL versions are installed.
.It Ar ssl_mode
SSL mode for the connection (default:
.Li prefer ) .
Accepted values:
.Li disable ,
.Li allow ,
.Li prefer ,
.Li require ,
.Li verify-ca ,
.Li verify-full .
Passed via
.Ev PGSSLMODE .
.It Ar ssl_cert
Path to the client SSL certificate file (PEM format).
Passed via
.Ev PGSSLCERT .
.It Ar ssl_cert_data
Inline PEM content of the client SSL certificate.
Alternative to
.Ar ssl_cert .
.It Ar ssl_key
Path to the client SSL private key file (PEM format).
Passed via
.Ev PGSSLKEY .
.It Ar ssl_key_data
Inline PEM content of the client SSL private key.
Alternative to
.Ar ssl_key .
.It Ar ssl_root_cert
Path to the root CA certificate used to verify the server (PEM format).
Passed via
.Ev PGSSLROOTCERT .
.It Ar ssl_root_cert_data
Inline PEM content of the root CA certificate.
Alternative to
.Ar ssl_root_cert .
.El
.Sh MANIFEST
Every snapshot produced by this integration contains a
.Pa /manifest.json
record (schema version 2) written before the backup data begins.
It captures the cluster state at the moment the backup was initiated.
.Pp
Top-level fields include the server version, host, port,
.Li pg_dump_version
.Pq logical backups ,
.Li pg_basebackup_version
.Pq physical backups ,
.Li cluster_system_identifier
.Pq unique cluster ID from pg_control_system() ,
.Li in_recovery
.Pq true when backup was taken from a hot standby ,
target database, dump format, and backup options.
.Bl -tag -width "cluster_config"
.It Li cluster_config
Key server GUCs:
.Li data_directory ,
.Li timezone ,
.Li max_connections ,
.Li wal_level ,
.Li server_encoding ,
.Li data_checksums ,
.Li block_size ,
.Li wal_block_size ,
.Li shared_preload_libraries ,
.Li lc_collate ,
.Li lc_ctype ,
.Li archive_mode ,
.Li archive_command_set
.Pq boolean; the command itself is not stored to avoid leaking credentials .
.It Li roles
All PostgreSQL roles with:
.Li superuser ,
.Li replication ,
.Li can_login ,
.Li create_db ,
.Li create_role ,
.Li inherit ,
.Li bypass_rls ,
.Li connection_limit ,
.Li valid_until ,
and
.Li member_of
.Pq list of parent roles .
.It Li tablespaces
All tablespaces with name, owner, filesystem location, and storage options.
.It Li databases
One entry per database: name, owner, encoding, collation,
.Li default_tablespace ,
.Li connection_limit ,
installed extensions
.Pq name, version, schema ,
schemas
.Pq name, owner ,
and a
.Li relations
array.
.It Li relations
One entry per relation (ordinary table, partitioned table, foreign table,
view, materialized view, sequence).
Each entry carries: schema, name, owner, persistence, kind, tablespace,
row estimates
.Pq Li row_estimate No from Li reltuples No and Li live_row_estimate No from autovacuum ,
column count, primary-key and trigger flags, row-level security flags,
partitioning metadata
.Pq Li is_partition No / Li partition_parent No / Li partition_strategy ,
storage options
.Pq reloptions ,
last vacuum and analyze timestamps,
.Li columns
.Pq position, name, type, nullable, default ,
.Li constraints
.Pq name, type code, column names ,
and
.Li indexes
.Pq name, method, definition, is_unique, is_primary, is_valid, is_partial, constraint_name .
.El
.Pp
Row counts are estimates from
.Li reltuples
.Pq pg_class
and
.Li n_live_tup
.Pq pg_stat_user_tables ,
not exact values.
Exact counts would require a full sequential scan of every table.
.Pp
Metadata collection is best-effort: if a query fails the affected field is
omitted and the backup continues normally.
.Sh PHYSICAL BACKUP
.Ss How it works
The source connector runs
.Xr pg_basebackup 1
.Fl D Fl Li - Fl F Li tar Fl X Li fetch ,
streaming a tar archive of the entire PostgreSQL data directory directly
from the server's replication interface.
Each file in the archive is stored as an individual record under
.Li data/
in the snapshot.
.Pp
No subpath may be specified in the URI:
.Xr pg_basebackup 1
always backs up the entire cluster.
.Pp
A
.Pa /manifest.json
record is written to the snapshot before the backup stream begins.
It contains the same full metadata as a logical backup: cluster config,
roles, tablespaces, database list, and per-database relation detail
.Pq schemas, tables, columns, indexes .
The
.Li pg_basebackup_version
field records the version of
.Xr pg_basebackup 1
used.
.Pp
There is no dedicated destination connector.
The snapshot contains plain files and can be restored with any
file-restore connector.
The restored directory is a valid PostgreSQL data directory that can be
started directly with a PostgreSQL binary of the same major version.
.Ss Prerequisites
.Xr pg_basebackup 1
must be present in
.Ev PATH .
The PostgreSQL server must have:
.Bl -bullet -compact
.It
.Li wal_level = replica
or higher in
.Pa postgresql.conf .
.It
A user with the
.Li REPLICATION
privilege (or a superuser).
.It
.Pa pg_hba.conf
allowing a replication connection from the backup host.
.El
.Ss Source options
.Bl -tag -width "pg_basebackup"
.It Ar location
Connection URI:
.Li postgres+bin://[user[:password]@]host[:port] .
A subpath is not allowed.
.It Ar host
Server hostname (default:
.Li localhost ) .
Overrides the URI host.
.It Ar port
Server port (default:
.Li 5432 ) .
Overrides the URI port.
.It Ar username
Replication username.
Overrides the URI user.
.It Ar password
Authentication password, passed via
.Ev PGPASSWORD .
Overrides the URI password.
.It Ar pg_bin_dir
Directory containing the PostgreSQL client binaries
.Pq pg_basebackup , psql .
When omitted, binaries are resolved via
.Ev PATH .
Useful when multiple PostgreSQL versions are installed.
.It Ar ssl_mode
SSL mode for the connection (default:
.Li prefer ) .
Accepted values:
.Li disable ,
.Li allow ,
.Li prefer ,
.Li require ,
.Li verify-ca ,
.Li verify-full .
Passed via
.Ev PGSSLMODE .
.It Ar ssl_cert
Path to the client SSL certificate file (PEM format).
Passed via
.Ev PGSSLCERT .
.It Ar ssl_cert_data
Inline PEM content of the client SSL certificate.
Alternative to
.Ar ssl_cert .
.It Ar ssl_key
Path to the client SSL private key file (PEM format).
Passed via
.Ev PGSSLKEY .
.It Ar ssl_key_data
Inline PEM content of the client SSL private key.
Alternative to
.Ar ssl_key .
.It Ar ssl_root_cert
Path to the root CA certificate used to verify the server (PEM format).
Passed via
.Ev PGSSLROOTCERT .
.It Ar ssl_root_cert_data
Inline PEM content of the root CA certificate.
Alternative to
.Ar ssl_root_cert .
.El
.Sh EXAMPLES
Back up a single database:
.Bd -literal -offset indent
$ plakar source add mypg postgres://postgres:secret@db.example.com/myapp
$ plakar backup @mypg
.Ed
.Pp
Back up all databases:
.Bd -literal -offset indent
$ plakar source add mypg postgres://postgres:secret@db.example.com
$ plakar backup @mypg
.Ed
.Pp
Restore a single database (created automatically from archive metadata):
.Bd -literal -offset indent
$ plakar destination add mypgdst postgres://postgres:secret@db.example.com \e
    create_db=true
$ plakar restore -to @mypgdst <snapid>
.Ed
.Pp
Restore all databases to a fresh server:
.Bd -literal -offset indent
$ plakar destination add mypgdst postgres://postgres:secret@db.example.com
$ plakar restore -to @mypgdst <snapid>
.Ed
.Pp
Physical backup of the entire cluster:
.Bd -literal -offset indent
$ plakar source add mypg postgres+bin://replicator:secret@db.example.com
$ plakar backup @mypg
.Ed
.Pp
Restore a physical backup to a local directory and start with Docker:
.Bd -literal -offset indent
$ plakar restore -to ./pgdata <snapid>
$ docker run --rm \e
    -v "$PWD/pgdata:/var/lib/postgresql/data" \e
    postgres:17
.Ed
.Sh SEE ALSO
.Xr pg_basebackup 1 ,
.Xr pg_dump 1 ,
.Xr pg_dumpall 1 ,
.Xr pg_restore 1 ,
.Xr psql 1