Restore a sql database backup into the docker

[Ramel]

I've separated the sql and previews(zou) storages as there are not on the same disk.

docker-compose.yml :

services:
  cgwire:
    image: cgwire/cgwire:latest
    container_name: kitsu
    init: true
    tty: true
    stdin_open: true
    ports:
      - 8012:80 # Change the port 8012 to your desired port.
      - 1080:1080
    volumes:
      - sql-storage:/var/lib/postgresql
      - zou-storage:/opt/zou/previews

volumes:
  zou-storage:
    driver: local
    driver_opts:
      type: 'none'
      o: 'bind'
      device: './zou-storage'
  sql-storage:
    driver: local
    driver_opts:
      type: 'none'
      o: 'bind'
      device: './sql-storage'

I have my backup file in './zou-storage' it will be available in '/opt/zou/previews'

# Create the Container
user@myhost:/opt/kitsu_backup$ docker-compose up -d
user@myhost:/opt/kitsu_backup$ docker exec -ti kitsu /bin/bash
root@bedd4dc4931e:/opt/zou# supervisorctl status
nginx                            RUNNING   pid 17, uptime 0:03:08
postgresql                       RUNNING   pid 14, uptime 0:03:08
redis                            RUNNING   pid 15, uptime 0:03:08
sendria                          RUNNING   pid 16, uptime 0:03:08
zou-processes:gunicorn           RUNNING   pid 12, uptime 0:03:08
zou-processes:gunicorn-events    RUNNING   pid 13, uptime 0:03:08
# Postgresql is running, ok!
# Copy the sql backup to tmp folder,  as we don't have access to it from postgresql user
root@bedd4dc4931e:/opt/zou# cp /opt/zou/previews/myhost-pgsql-zoudb.20260101.sql /tmp/
root@bedd4dc4931e:/opt/zou# chmod 777 /tmp/myhost-pgsql-zoudb.20260101.sql
# Stop all services, keep only postgresql
root@bedd4dc4931e:/opt/zou# supervisorctl stop all
sendria: stopped
nginx: stopped
postgresql: stopped
redis: stopped
zou-processes:gunicorn-events: stopped
zou-processes:gunicorn: stopped
root@bedd4dc4931e:/opt/zou# supervisorctl start postgresql
postgresql: started
root@bedd4dc4931e:/opt/zou# su - postgres
postgres@bedd4dc4931e:~$ psql -c "DROP DATABASE zoudb;"
DROP DATABASE
postgres@bedd4dc4931e:~$ 
postgres@bedd4dc4931e:~$ createdb -T template0 -E UTF8 --owner root zoudb
postgres@bedd4dc4931e:~$ psql -U postgres -d zoudb -c "GRANT ALL ON SCHEMA public TO root;"
postgres@bedd4dc4931e:~$ psql -U postgres -d zoudb -c "ALTER USER root WITH PASSWORD 'mypassword';"
postgres@bedd4dc4931e:~$ psql -U postgres -d zoudb -1 --set=ON_ERROR_STOP=1 -f /tmp/ns3151156-pgsql-zoudb.20260123.sql

postgres@bedd4dc4931e:~$ psql -U postgres -d zoudb -c "\dt"
                               List of relations
 Schema |                       Name                        | Type  |  Owner
--------+---------------------------------------------------+-------+----------
 public | alembic_version                                   | table | postgres
 […]
# It seems that we need to set all to "root"
# Grant Schema ownership (enables creating/modifying objects)
postgres@bedd4dc4931e:~$ psql -U postgres -d zoudb -c "ALTER SCHEMA public OWNER TO root;"
# Change the owner of ALL tables
postgres@bedd4dc4931e:~$ psql -U postgres -d zoudb -Atc "SELECT 'ALTER TABLE public.' || quote_ident(tablename) || ' OWNER TO root;' FROM pg_tables WHERE schemaname = 'public'" | psql -U postgres -d zoudb
# Change the owner of ALL sequences (CRITICAL for ID/Serial errors)
postgres@bedd4dc4931e:~$ psql -U postgres -d zoudb -Atc "SELECT 'ALTER SEQUENCE public.' || quote_ident(relname) || ' OWNER TO root;' FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'S' AND n.nspname = 'public'" | psql -U postgres -d zoudb
# Change the owner of ALL views
postgres@bedd4dc4931e:~$ psql -U postgres -d zoudb -Atc "SELECT 'ALTER VIEW public.' || quote_ident(viewname) || ' OWNER TO root;' FROM pg_views WHERE schemaname = 'public'" | psql -U postgres -d zoudb
postgres@bedd4dc4931e:~$ exit
logout
root@bedd4dc4931e:/opt/zou# supervisorctl stop all
postgresql: stopped
root@bedd4dc4931e:/opt/zou# supervisorctl start all
zou-processes:gunicorn: started
zou-processes:gunicorn-events: started
postgresql: started
redis: started
sendria: started
nginx: started
root@bedd4dc4931e:/opt/zou#

The backup should be available in your browser on port 8012, as set in the docker-compose file.

# Not needed, just in case
root@bedd4dc4931e:/opt/zou# sed -i "s/DB_PASSWORD=''/DB_PASSWORD='mypassword'/g" /etc/supervisord.conf
##################
# Restart all from start, cleanup
user@myhost:/opt/kitsu_backup$ docker-compose down -v --rmi all
user@myhost:/opt/kitsu_backup$ sudo rm -rf  sql-storage/14
user@myhost:/opt/kitsu_backup$ sudo rm -rf  sql-storage/.*