2.5 KiB
title, eleventyNavigation
| title | eleventyNavigation | ||||
|---|---|---|---|---|---|
| PostgreSQL |
|
New in version 1.4.0 To backup
PostgreSQL with borgmatic, use the postgresql_databases: hook. For instance:
postgresql_databases:
- name: users
See below for the full set of configuration options available, including hostname, PostgreSQL username, password, etc.
Permissions
Dumping
In order to dump your database as part of creating a backup, the PostgreSQL user
performing the dump needs relevant permissions. A common way to accomplish this
is to connect as the PostgreSQL superuser, usually postgres. However, if you'd
like to connect as a non-superuser, that user will need permissions to:
- connect to the database
- read tables and sequences
Here is one way to do that with PostgreSQL 14+:
GRANT CONNECT ON DATABASE example_database TO database_user;
GRANT pg_read_all_data TO database_user;
And here is an alternate way to accomplish something similar that limits read access to a particular schema instead of the whole cluster. Replace "public" with the name of the schema you're using:
GRANT CONNECT ON DATABASE example_database TO database_user;
GRANT USAGE ON SCHEMA public TO database_user;
-- Grant read privileges on all current and future tables in the schema.
GRANT SELECT ON ALL TABLES IN SCHEMA public TO database_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO database_user;
-- Grant read privileges on all current and future indexes in the schema.
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO database_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO database_user;
Restoring
If you also want this user to be able to restore your database, and you're not
restoring as the PostgreSQL superuser, then you'll need to grant write and
ANALYZE permissions as well. For instance:
GRANT pg_write_all_data TO database_user;
GRANT pg_maintain TO database_user;
Or you can perform schema-level grants if you prefer.
For more information, see the PostgreSQL documentation on PostgreSQL predefined roles and privileges.
Full configuration
{% include snippet/configuration/sample.md %}
{% include borgmatic/postgresql_databases.yaml %}