How to backup a AWX postgres database in docker.

postgres.docker.backup.txt

To backup:
docker exec -u <your_postgres_user> <postgres_container_name> pg_dump -Fc <database_name_here> > db.dump

To drop db (Don't do it on production, for test purpose only!!!):
docker exec -u <your_postgres_user> <postgres_container_name> psql -c 'DROP DATABASE <your_db_name>'

To restore:
docker exec -i -u <your_postgres_user> <postgres_container_name> pg_restore -C -d postgres < db.dump

working example for awx postgres database
backup:
docker exec -u postgres postgres pg_dump -Fc awx > `date +%m.%d.%y_%H.%M.%S`_awx_db.dump

RESTORE:
docker stop awx_task
docker stop awx_web
docker stop memcached

rename current db:
docker exec -u postgres postgres psql postgres awx -c 'ALTER DATABASE "awx" RENAME TO "awx_01_03_18"'

restore AWX db:
docker exec -i -u postgres postgres pg_restore -C -d postgres < 01.03.18_03.17.33_awx_db.dump

docker start memcached
docker start awx_web
docker start awx_task

if you are happy with everything you can drop your other database:
docker exec -u postgres postgres psql -c 'DROP DATABASE awx_01_03_18'

i have tried this MULTIPLE times and had someone a bit smarter then me try it......EVERY SINGLE time we get; pg_restore: [archiver] input file does not appear to be a valid archive (too short?).
It appears to have an issue with the dump file as it relates to the awx account. Sorry for the caps I'm extremely frustrated. Been at this for a day and a half. None of the fixes as it relates to that error do anything

that error above is what we get on trying to restore the DB

Restore step fails for me as well.

[root@awx pgrestore]# docker exec -i -u postgres awx_postgres pg_restore -C -d postgres < awx_20_11_19_dump.tar
pg_restore: [archiver (db)] connection to database "postgres" failed: FATAL:  role "postgres" does not exist
read unix @->/var/run/docker.sock: read: connection reset by peer

root@65865dfc21d5:/# psql -U awx -l
                               List of databases
     Name     | Owner | Encoding |  Collate   |   Ctype    | Access privileges 
--------------+-------+----------+------------+------------+-------------------
 awx_21_11_19 | awx   | UTF8     | en_US.utf8 | en_US.utf8 | 
 postgres     | awx   | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0    | awx   | UTF8     | en_US.utf8 | en_US.utf8 | =c/awx           +
              |       |          |            |            | awx=CTc/awx
 template1    | awx   | UTF8     | en_US.utf8 | en_US.utf8 | =c/awx           +
              |       |          |            |            | awx=CTc/awx
(4 rows)

I've managed to restore the database. For anybody else, this is the updated commands inside a shell script. Edit to fit your needs.

#!/bin/bash

echo "MAKE SURE TO EDIT dump filename and dump directory location."
read -n 1 -s -r -p "Press any key to continue"

echo "Stopping awx_task container"
docker stop awx_task
echo "done"

echo "Stopping awx_web container"
docker stop awx_web
echo "done"

echo "Stopping memcached container"
docker stop awx_memcached
echo "done"

echo "Renaming database awx"
docker exec -u postgres awx_postgres psql postgres awx -c 'ALTER DATABASE "awx" RENAME TO "awx_21_11_19"'
echo "done"

echo "List databases"
docker exec awx_postgres psql -U awx -l

echo "Creating pgrestore directory inside awx_postgres container"
docker exec -d awx_postgres mkdir /pgrestore

echo "Copy postgres dump to awx_postgres:pgrestore"
docker cp /opt/pgrestore/awx_21_11_19_dump.tar awx_postgres:pgrestore

echo "Restoring database awx"
docker exec awx_postgres pg_restore -U awx -C -c /pgrestore/awx_20_11_19_dump.tar
echo "done"

echo "Starting memcached container"
docker start awx_memcached
echo "done"

echo "Starting awx_web container"
docker start awx_web
echo "done"

echo "Starting awx_task container"
docker start awx_task
echo "done"

echo "AWX should be upgrading once awx_web is properly started up"
echo "Please check $HOSTNAME"

Thanks Nicolai, sorry guys been really busy haven't had a chance to look into this.

I JUST finished revamping all of my automation into more industry standard format (roles, handlers, Jinja templates, etc) I've just got to finish revamping all the new hire automation and I should have time to attempt this this week. Thanks in advance Nicolai. I hope this does the trick.

LDAP settings didnt carry over. Also none of the job templates. And none of the project information either. The PG Restore looked like it went fine (no errors). The Tower-CLi seemed to go aok with the exception of a bunch of errors related to what looks like the machine inventory from my vCenter instance and it barked about my Azure Git Hub repo creds.

I'll try again to be certain

ok so . i was able to restore everything BUT LDAP config. Thanks for helping with restoring postgres data

I have seen a few instances of people saying that using ActiveDirectoryGroupType wouldnt actually save in the database so I'm going to try again with NestedActiveDirectoryGroupType

yeah that didnt work either....same thing.
And to be clear it appears everything carried over except for the LDAP settings and my job templates. So weird. notifications, credentials, inventory.......all carried over but those 2 small but important things.

Got it to work with an external PGDB. had some weird issues using HA pulling from Azure DevOps and loadbalancing. I think I was doing too much. I'm starting from scratch with a bit more a simpler setup

Got it to work with an external PGDB. had some weird issues using HA pulling from Azure DevOps and loadbalancing. I think I was doing too much. I'm starting from scratch with a bit more a simpler setup

@AechLyons Do you have a git repo with that setup? I'm interested in using Azure Devops with AWX.

Yeah it was surprisingly easy to setup. Select clone repo, use the http version of the repo but delete the user in the URL and replace it with a generated token. You should be off to the races. Let me know if that doesn't work.

It makes managing your evolving books easy (especially if you flag it to check for the latest code change before any job run) but I noticed it makes. The job progress window a bit lazy (had to refresh to see progress realtime). Ymmv

The instructions here don't seem to work now, dumped it with:

# docker exec -u awx awx_postgres pg_dump -Fc awx > /var/lib/awx/projects/awx-db_`date +%y.%m.%d_%H.%M.%S`.dump

Restore it with:

root@AWX-2-panel:~# docker stop awx_task
awx_task
root@AWX-2-panel:~# docker stop awx_web
awx_web

root@AWX-2-panel:~# docker exec -u postgres awx_postgres psql postgres awx -c 'ALTER DATABASE "awx" RENAME TO "awx-db_21.01.04_13.02.09"'

root@AWX-2-panel:~# docker exec awx_postgres psql -U awx -l
                                     List of databases
           Name           | Owner | Encoding |  Collate   |   Ctype    | Access privileges 
--------------------------+-------+----------+------------+------------+-------------------
 awx-db_21.01.04_13.02.09 | awx   | UTF8     | en_US.utf8 | en_US.utf8 | 
 postgres                 | awx   | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0                | awx   | UTF8     | en_US.utf8 | en_US.utf8 | =c/awx           +
                          |       |          |            |            | awx=CTc/awx
 template1                | awx   | UTF8     | en_US.utf8 | en_US.utf8 | =c/awx           +
                          |       |          |            |            | awx=CTc/awx
(4 rows)

root@AWX-2-panel:~# docker exec -d awx_postgres mkdir /pgrestore
root@AWX-2-panel:~# docker cp /root/awx-db_21.01.04_13.02.09.dump awx_postgres:pgrestore

root@AWX-2-panel:~# docker exec awx_postgres pg_restore -U awx -C -c /pgrestore/awx-db_21.01.04_13.02.09.dump
pg_restore: [archiver] input file does not appear to be a valid archive (too short?)

Trying to move the application from one Debian 10 machine to another Debian 10 machine. Upgrading from 15.01 to 16.0.0.

After installing AWX with the same credentials, I cleared out /var/lib/awx/projects/ and updated it's contents with that of the previous server, then tried to perform this DB import.

These seemed to work:

# docker exec -t awx_postgres pg_dump -U awx awx > /var/lib/awx/projects/awx-dump.sql

# docker exec -t awx_postgres psql -U awx awx < /var/lib/awx/projects/awx-dump.sql

or

root@AWX-3-panel:~# docker stop awx_task

awx_task

root@AWX-3-panel:~# docker stop awx_web

awx_web

root@AWX-3-panel:~# cp /var/lib/awx/projects/awx-dump.sql /root/.awx/pgdocker/10/data/

root@AWX-3-panel:~# docker exec -it awx_postgres /bin/bash

root@ce48e2584014:/#

root@ce48e2584014:/# dropdb -U awx awx

root@ce48e2584014:/# createdb -U awx awx

root@ce48e2584014:/# psql -U awx awx < /var/lib/postgresql/data/awx-dump.sql

root@ce48e2584014:/# exit

exit

root@AWX-3-panel:~# docker start awx_task

awx_task

root@AWX-3-panel:~# docker start awx_web

awx_web