AWX/Database Backup

AWX V19 - Backup and Restore
https://groups.google.com/g/awx-project/c/y0k1hQfI77E

1. Export Docker DB like: pg_dump --username awx awx --format=c" > ./psql_dump.sql
2. Drop AWX-Operator DB: minikube kubectl -- exec -it awx-postgres-0 -- dropdb -U awx awx ( failed here since  the db was  currently  being  used)

3. Create new DB: minikube kubectl -- exec -it awx-postgres-0 -- createdb -U awx awx
4. Restore DB: minikube kubectl -- exec -it awx-postgres-0 -- pg_restore -U awx -d awx < psql_dump.sql

AWX Backup Readme
https://github.com/ansible/awx-operator/blob/devel/roles/backup/README.md
AWX Restore Readme
https://github.com/ansible/awx-operator/blob/devel/roles/restore/README.md

---

k -n awx get all


pod/awx-awx-postgres-15-0

k -n awx exec -it awx-awx-postgres-15-0 -- /bin/bash


pg_dump -U postgres awx17 > /var/lib/pgsql/12/backups/awx17.bak_02032023


bash-5.1$ pg_dump -U postgres awx > dumb.db
bash-5.1$ pg_dump -U postgres awx --format=c > dumb2.db

https://groups.google.com/g/awx-project/c/y0k1hQfI77E

/opt/app-root/src

# k -n awx cp awx_psql_dump.sql awx-awx-postgres-15-0:/opt/app-root/src/
k -n awx cp awx_psql_dump.sql awx-awx-postgres-15-0:/opt/app-root/src/ -c postgres

dropdb -U awx awx
createdb -U awx awx
pg_restore -U awx -d awx < psql_dump.sql

createdb -U awx awx
pg_restore -U awx -d awx < awx_psql_dump.sql

--

Default AWX Configuration causes postgres invalid password · Issue #530 · ansible/awx-operator · GitHub
https://github.com/ansible/awx-operator/issues/530

If it still doesn't work and we keep getting the same error in the awx-postgres-0 log, we will connect to the database:

   # kubectl exec -it awx-postgres-0 -- psql -U awx
We change the password to the one we have specified in the previous secrets:

   # ALTER USER awx WITH PASSWORD 'yournewpass';

At this point you should be able to connect the awx pod.

---

DB not persisting

Container reinit database on rebuild
https://github.com/docker-library/postgres/issues/761

"You need to change the volume to /var/lib/postgresql/data, otherwise your volume isn't saving anything but an empty folder. (The image already has a VOLUME defined at /var/lib/postgresql/data so an anonymous volume is created there if one is not defined; usually docker-compose tries hard to reuse any anonymous volumes, so it works most of the time.)"

--

Add an option to skip DB init check · Issue #1220 · docker-library/postgres
https://github.com/docker-library/postgres/issues/1220

# echo $PGDATA
/var/lib/postgresql
# cat /var/lib/postgresql/postgresql.conf | grep data_directory
data_directory = '/var/lib/postgresql/data'
# postgres -C data_directory
/var/lib/postgresql/data

Currently, checking if the DB is initialized is by checking if $PGDATA/PG_VERSION if exist.
However, PGDATA may not be the real data directory.
The real data directory can be overwritten by $PGDATA/postgresql.conf with data_directory = .

schema public

ERROR: permission denied for schema public

Now your flow should look like this:

CREATE DATABASE EXAMPLE_DB;
CREATE USER EXAMPLE_USER WITH ENCRYPTED PASSWORD 'Sup3rS3cret';
GRANT ALL PRIVILEGES ON DATABASE EXAMPLE_DB TO EXAMPLE_USER;
\c EXAMPLE_DB postgres
# You are now connected to database "EXAMPLE_DB" as user "postgres".
GRANT ALL ON SCHEMA public TO EXAMPLE_USER;

ref:

database - Why am I getting a permission denied error for schema public on pgAdmin 4? - Stack Overflow
https://stackoverflow.com/questions/67276391/why-am-i-getting-a-permission-denied-error-for-schema-public-on-pgadmin-4