Postgres
Note
In these examples, green
is the live database.
Note
In these examples, the owner name is the same as the database name.
List current databases:
psql -U postgres -c "\l"
Backup database:
pg_dump -U postgres green -f ~/repo/temp/out.sql
Restore data (from green
to the empty blue
database):
psql -U blue -d blue -f out.sql 2> out.log
Important
Remember to check out.log
.
Logging / Journal
sys-systemd-journalctl-postgres
Microsoft Azure Database for PostgreSQL
Microsoft recommend using PgBouncer with Azure Database for PostgreSQL: Setup PgBouncer connection pooling proxy with Azure DB for PostgreSQL The configuration below assumes you will be using PgBouncer.
The Microsoft Azure Database for PostgreSQL has an extra layer of security (authentication). To configure this:
Create a db/postgres_settings.sls
file in your pillar e.g:
# db/postgres_settings.sls
postgres_settings:
bouncer: True
bouncer_listen_on_all_addresses: True
listen_address: mypgserver.postgres.database.azure.com
postgres_pass: azure-postgres-db-password
postgres_ssh: verify-full
postgres_user: azure-postgres-db-user
postgres_host: mypgserver
And add it to the config for the server e.g:
# top.sls
'test-a':
- db.postgres_settings
- sites.my
Add the db_user
to the sites
pillar e.g:
# sites/my.sls
sites:
www.hatherleigh.info:
package: hatherleigh_info
profile: django
db_user: "www_hatherleigh_info@mypgserver"
In your Django project, settings/production.py
:
DOMAIN = get_env_variable("DOMAIN")
DATABASE_NAME = DOMAIN.replace(".", "_").replace("-", "_")
DATABASES = {
"default": {
"ENGINE": "django.db.backends.postgresql_psycopg2",
"NAME": DATABASE_NAME,
"USER": get_env_variable("DB_USER"),
"PASSWORD": "",
"HOST": "localhost",
"PORT": "6432",
"DISABLE_SERVER_SIDE_CURSORS": True,
}
}
Warning
Don’t forget to DISABLE_SERVER_SIDE_CURSORS
.
Read Only User
Add database_users
to your pillar file.
The key is the name of the read only user e.g. bi_www_hatherleigh_info
:
# sites/my.sls
sites:
www.hatherleigh.info:
db_pass: MyPassword
db_type: psql
database_users:
bi_www_hatherleigh_info:
pass: MyReadOnlyPassword
env:
activiti_host: "localhost"
activiti_pass: "kermit"
The Salt state will create the read only users in the PGBouncer files.
Tip
Prefix the name of the read only user with bi_
(business information / reporting).
Use fabric
to create the read only users e.g:
fab create-database-users www.hatherleigh.info
Warning
The user will not have access to database tables created after running this command. To give the user access to new tables, just run the command again.
Upgrade Cluster
After a do-release-upgrade
, you will probably be left with several database
clusters e.g:
# pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
9.3 main 5432 online postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log
9.5 main 5433 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
10 main 5434 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
Tip
Example commands for moving from Postgres 10 to 12, can be found here: Postgres Upgrade Cluster…
To move the data from Postgres 12 to Postgres 14, run the following as
root
:
# list the clusters
pg_lsclusters
# drop the new (empty) cluster
pg_dropcluster 14 main --stop
# migrate the data from 12
pg_upgradecluster 12 main
# remove the 12 cluster
pg_dropcluster 12 main --stop
# remove the old database engines
apt remove postgresql-12
# list the clusters
pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
14 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log