Linux Docker PostgreSQL ArcSDE Geodatabases

[MarceloMarques]

_________________________________________________________________

_________________________________________________________________

Running a Linux Docker container with PostgreSQL for the ArcSDE Geodatabase, while utilizing the Esri ST_Geometry library alongside PostGIS spatial data types, is an effective way to manage spatial data environments. This setup allows for enhanced functionality and optimized performance in geographic information systems (GIS) applications.
_________________________________________________________________

--1. Install Docker on Linux

Linux Docker Installation - Esri Community

_________________________________________________________________

--2. PostgreSQL on Docker

https://www.docker.com/blog/how-to-use-the-postgres-docker-official-image/

_________________________________________________________________
--2.1 docker postgresql image

docker pull postgres:15.13
docker pull postgres:16.9
docker pull postgres:16.8

docker image ls

_________________________________________________________________
--2.2 docker container postgresql

--create volume

docker volume ls
docker volume rm pg-db-16-data
docker volume create pg-db-16-data
docker volume rm pg-db-16-tbs
docker volume create pg-db-16-tbs

--NOTES:
--1. must follow the exactly order for the parameters or it fails to create the container!!!
--2. invalid value for parameter "port": "5437:5432"
--3. the -p option must come before the image name

Examples:

docker run --name pg-db-16 --restart=always \
-v c:/temp/my-postgres.conf:/etc/postgresql/postgresql.conf \
-e POSTGRES_PASSWORD="mcs7dmin" -p 5432:5432 \
-v pg-db-16-data:/var/lib/postgresql/data \
-d postgres:16.9 \
-c config_file=/etc/postgresql/postgresql.conf

docker run --name pg-db-16 --restart=always \
-v c:/temp/my-postgres.conf:/etc/postgresql/postgresql.conf \
-e POSTGRES_PASSWORD="mcs7dmin" -p 5437:5432 \
-v pg-db-16-data:/var/lib/postgresql/data \
-d postgres:16.9 \
-c config_file=/etc/postgresql/postgresql.conf

docker run --name pg-db-16 --restart=always \
-e POSTGRES_PASSWORD="mcs7dmin" -p 5432:5432 \
-v pg-db-16-data:/var/lib/postgresql/data \
-d postgres:16.9

docker run --name pg-db-16 --restart=always \
-e POSTGRES_PASSWORD="mcs7dmin" -p 5437:5432 \
-v pg-db-16-data:/var/lib/postgresql/data \
-d postgres:16.9

docker run --name pg-db-16 --restart=always \ 
-e POSTGRES_PASSWORD="mcs7dmin" -p 5437:5432 \
-v pg-db-16-data:/var/lib/postgresql/data \
-d postgres:16.8

docker volume rm pg-db-16-data
docker volume create pg-db-16-data
docker volume rm pg-db-16-tbs
docker volume create pg-db-16-tbs

docker run --name pg-db-16 --restart=always \
-e POSTGRES_PASSWORD="mcs7dmin" -p 5437:5432 \
-v pg-db-16-data:/var/lib/postgresql/data \
-v pg-db-16-tbs:/var/lib/postgresql/tbs 
-d postgres:16.9

--(Optional): mount a host directory into the container for postgresql backups using -F d (directory option) for large databases

docker run --name pg-db-16 --restart=always \
-e POSTGRES_PASSWORD="mcs7dmin" -p 5437:5432 \
-v pg-db-16-data:/var/lib/postgresql/data \
-v pg-db-16-tbs:/var/lib/postgresql/tbs \
-v /dockerhome/postgresql/backups/pg-db-16/pg-dump:/var/lib/postgresql/backup \
-d postgres:16.8

docker run --name pg-db-16 --restart=always \
-e POSTGRES_PASSWORD="mcs7dmin" -p 5437:5432 \
-v pg-db-16-data:/var/lib/postgresql/data \
-v pg-db-16-tbs:/var/lib/postgresql/tbs \
-v /dockerhome/postgresql/backups/pg-db-16/pg-dump:/var/lib/postgresql/backup \
-d postgres:16.9

--set the permissions for the folders in the docker container

docker exec -it -u root pg-db-16 bash
chown -R postgres:postgres /var/lib/postgresql/backup
chown -R postgres:postgres /var/lib/postgresql/tbs
ls -la /var/lib/postgresql/

--Solution: Change Ownership from the Host
docker exec -it -u root pg-db-16 bash
whoami
--root
id -a postgres
--uid=999(postgres) gid=999(postgres) groups=999(postgres),101(ssl-cert)
exit

docker volume inspect pg-db-16-data
--"Mountpoint": "/dockerhome/dockerdata/volumes/pg-db-16-data/_data"

docker volume inspect pg-db-16-tbs
--"Mountpoint": "/dockerhome/dockerdata/volumes/pg-db-16-tbs/_data"

--Change ownership on the host
sudo chown -R 999:999 /dockerhome/dockerdata/volumes/pg-db-16-data/_data
sudo chown -R 999:999 /dockerhome/dockerdata/volumes/pg-db-16-tbs/_data
sudo chown -R 999:999 /dockerhome/postgresql/backups/pg-db-16/pg-dump

docker exec -it pg-db-16 bash
ls -la /var/lib/postgresql
exit

--check the docker container logs for errors.

docker logs --tail 50 pg-db-16

--list the docker containers

docker ps -a

docker inspect 5b72d963060b | grep IPAddress
IPAddress": "172.17.0.2"

docker port pg-db-16

docker logs pg-db-16
docker logs --tail 50 pg-db-16
docker logs -f pg-db-16

docker stop pg-db-16
docker start pg-db-16
docker restart pg-db-16

_________________________________________________________________
--2.3 docker container remove

docker stop pg-db-16
docker rm pg-db-16

_________________________________________________________________
--2.4 docker container upgrade from PostgreSQL 16.8 to PostgreSQL 16.9

--1. stop the container

docker stop pg-db-16

docker ps -a

--2. keep the docker volumes that were used to create the container

docker volume ls

--the volume pg-db-16-data has the PG instance configuration
--the volume pg-db-16-tbs has the PG databases tablespaces

--3. drop the old docker container running pg_16.8

docker rm pg-db-16
docker ps -a

--4. recreate the docker container using the pg_16.9 image

docker run --name pg-db-16 --restart=always \
-e POSTGRES_PASSWORD="mcs7dmin" -p 5437:5432 \
-v pg-db-16-data:/var/lib/postgresql/data \
-v pg-db-16-tbs:/var/lib/postgresql/tbs \
-v /dockerhome/postgresql/backups/pg-db-16/pg-dump:/var/lib/postgresql/backup \
-d postgres:16.9

docker ps -a

docker logs --tail 50 pg-db-16

--NOTES:
--a. using the same volumes, all the data will be preserved
--b. if you are upgrading a major new version (like 15 to 16),
-- PostgreSQL will automatically run any necessary data migration when it starts

--5. copy the esri st_geometry library to the docker container again

docker cp /dockerhome/esri_st_geom_lib/st_geometry.so 
       pg-db-16:/usr/lib/postgresql/16/lib/st_geometry.so

docker exec -it pg-db-16 bash
chown root:root /usr/lib/postgresql/16/lib/st_geometry.so
ls -la /usr/lib/postgresql/16/lib/
exit

--6. set the permissions for the folders in the docker container

docker exec -it pg-db-16 bash
chown -R postgres:postgres /var/lib/postgresql/backup
chown -R postgres:postgres /var/lib/postgresql/tbs
ls -la /var/lib/postgresql/
exit

--7. install postgis extension again in the docker container

docker exec -it pg-db-16 bash
apt-get update
apt-get install postgresql-16-postgis-3
dpkg -l | grep postgis
exit

--8. upgrade the postgis extension in each postgresql database

 FAQ - Frequent Asked Questions
 How to Upgrade the PostgreSQL and PostGIS version for the Ent. Geodatabase on Windows
 How to Upgrade the PostgreSQL and PostGIS version for the Ent. Geodatabase on Linux

cd C:\Program Files\pgAdmin 4\runtime

psql -h ps026300 -p 5437 -U postgres -d postgres

\list 

\c pm         <<-- connect to each pg database to upgrade the postgis extension

SELECT postgis_full_version();
ALTER EXTENSION postgis UPDATE;
SELECT postgis_extensions_upgrade();
ALTER EXTENSION postgis UPDATE;
SELECT postgis_full_version();

exit

_________________________________________________________________

--2.5 docker container psql

docker exec -it pg-db-16 psql -U postgres -d postgres -p 5432

_________________________________________________________________
--2.6 docker container terminal

docker exec -it pg-db-16 sh
docker exec -it pg-db-16 bash
docker exec -it -u root pg-db-16 bash
docker exec -it -u postgres pg-db-16 bash

_________________________________________________________________
--2.7 how to export a file from the docker container

docker cp pg-db-16:/var/lib/pgsql/data/pg_hba.conf 
       /data/docker/postgres/16_9/pg_hba.conf

docker cp pg-db-16:/var/lib/pgsql/data/postgresql.conf 
       /data/docker/postgres/16_9/postgresql.conf

_________________________________________________________________

--2.8 how to copy a file to the docker container

docker cp /data/docker/postgres/16_9/pg_hba.conf 
       pg-db-16:/var/lib/pgsql/data/pg_hba.conf

docker cp /data/docker/postgres/16_9/postgresql.conf 
       pg-db-16:/var/lib/pgsql/data/postgresql.conf

pg_hba.conf
#IPV4 local connections:
host all all 0.0.0.0/0 md5

_________________________________________________________________

--2.9 how to delete a file in the docker container

docker exec container_name rm -f /path/to/file.txt

docker exec pg-db-16 rm -f /var/lib/postgresql/data/pg_hba_5432.conf

_________________________________________________________________
--3. postgresql parameters

docker exec -it pg-db-16 psql -U postgres -d postgres -p 5432

--parameters
SELECT name, setting, unit, short_desc
FROM pg_settings
WHERE name = 'max_connections';

ALTER SYSTEM SET work_mem = '64MB';
ALTER SYSTEM SET max_connections = 1000;
SELECT pg_reload_conf();

exit;

docker run -d --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword postgres \
-c shared_buffers=256MB -c max_connections=200

docker run -d --name pg-db-16 -e POSTGRES_PASSWORD=mcs7dmin \
-c shared_buffers=256MB -c max_connections=200

docker run -d --name pg-db-16 -e POSTGRES_PASSWORD=mcs7dmin -c max_connections=10000

docker exec -it <container_name> psql -U <username> -d <database> \
-c "ALTER SYSTEM SET work_mem = '64MB';"

docker exec -it <container_name> psql -U <username> -d <database> \
-c "SELECT pg_reload_conf();"

docker stop pg-db-16
docker start pg-db-16
docker restart pg-db-16

Example:

docker exec -it pg-db-16 psql -U postgres -d postgres \
-c "ALTER SYSTEM SET max_connections = 7777;"

docker exec -it pg-db-16 psql -U postgres -d postgres -c "SELECT pg_reload_conf();" 
--NOTE: does not refresh the parameter change
--it is necessary to stop/start the container!!!

docker restart pg-db-16

_________________________________________________________________
--4. postgresql tablespaces

docker exec -it pg-db-16 psql -U postgres -d postgres -p 5432

/db

SELECT spcname AS "Name",
pg_catalog.pg_get_userbyid(spcowner) AS "Owner",
pg_catalog.pg_tablespace_location(oid) AS "Location"
FROM pg_catalog.pg_tablespace;

exit;

_________________________________________________________________
--5. connect from remote machine

cd C:\Program Files\pgAdmin 4\runtime

psql --version

psql -h ps026300 -p 5437 -U postgres -d postgres

_________________________________________________________________
--6. COPY THE ESRI ST_GEOMETRY LIBRARY TO THE DOCKER CONTAINER

--docker container terminal
docker exec -it pg-db-16 sh
docker exec -it pg-db-16 bash

--where is postgresql installed

root@27fe7e13d0b9:/# which postgres
/usr/lib/postgresql/16/bin/postgres

--create the folder for the pg tablespaces of the geodatabases
mkdir -p /var/lib/postgresql/tbs
chown -R postgres:postgres /var/lib/postgresql/tbs

exit;

docker cp /dockerhome/esri_st_geom_lib/st_geometry.so 
       pg-db-16:/usr/lib/postgresql/16/lib/st_geometry.so

docker exec -it pg-db-16 bash
chown root:root /usr/lib/postgresql/16/lib/st_geometry.so
ls -la /usr/lib/postgresql/16/lib/
exit;

_________________________________________________________________
--7. HOW TO INSTALL POSTGIS IN A DOCKER CONTAINER

Option 1: Use the official PostGIS Docker image

postgis/postgis:16-3.5 is the image for PostgreSQL 16 with PostGIS 3.5.

You can verify the version tags here: https://hub.docker.com/r/postgis/postgis/tags

docker pull postgis/postgis:16-3.5
docker pull postgis/postgis:16-3.4

docker image ls

Option 2: Install PostGIS manually in a running container

docker exec -it pg-db-16 bash

_________________________________________________________________
--debian linux install package (docker container is debian linux !!!)

apt-get update

apt search postgis | grep postgresql-16 | sort
apt-cache madison postgis

apt-get install postgresql-16-postgis-3

apt list --installed | grep postgis

dpkg -l | grep postgis

exit

--how to install a specific postgis version

# apt search postgis | grep postgresql-16 | sort

WARNING: apt does not have a stable CLI interface. Use with caution in scripts.

debug symbols for postgresql-16-postgis-3
postgresql-16-mobilitydb/bookworm-pgdg 1.2.0-2.pgdg120+1 amd64
postgresql-16-pgrouting/bookworm-pgdg 3.8.0-1~exp1.pgdg120+1 amd64
postgresql-16-pgrouting-doc/bookworm-pgdg 3.7.0-1.pgdg120+1 all
postgresql-16-pgrouting-scripts/bookworm-pgdg 3.8.0-1~exp1.pgdg120+1 all
postgresql-16-postgis-3/bookworm-pgdg 3.5.3+dfsg-1~exp1.pgdg120+1 amd64
postgresql-16-postgis-3-dbgsym/bookworm-pgdg 3.5.3+dfsg-1~exp1.pgdg120+1 amd64
postgresql-16-postgis-3-scripts/bookworm-pgdg 3.5.3+dfsg-1~exp1.pgdg120+1 all

# apt-cache madison postgis
postgis | 3.5.3+dfsg-1~exp1.pgdg120+1 | http://apt.postgresql.org/pub/repos/apt bookworm-pgdg/main amd64 Packages
postgis | 3.3.2+dfsg-1+b1 | http://deb.debian.org/debian bookworm/main amd64 Packages

# apt-get install postgis=3.3.2+dfsg-1+b1
# apt list --installed | grep postgis

--debian linux how to remove a package

apt remove package-name
apt purge package-name
apt autoremove

or

dpkg -r package-name
dpkg --purge package-name

_________________________________________________________________

--8. Create the postgis extension in the PG database

docker exec -it your_container_name psql -U postgres -d your_database \
-c "CREATE EXTENSION postgis;"

TIP: To enable the PG geodatabase to support both spatial types: esri st_geometry and postgis, follow these steps:

  - Do not create the postgis extension in the database before creating the ArcSDE Repository.

  - First, create the ArcSDE Repository using the esri st_geometry option.

  - After the ArcSDE Repository is created, then add the postgis extension.

_________________________________________________________________

--9. PostgreSQL Geodatabase Best Practices

Use the database guide books and database templates scripts best practices to create the PostgreSQL Geodatabase.

 Mapping and Charting Solutions (MCS) Enterprise Databases Best Practices

• FAQ - Frequent Asked Questions
• Guide Books ArcGIS Pro
• Database Connections Best Practices
• Database Template Scripts
  
  

   PostgreSQL
     ArcGIS Pro 3.x & ArcMap 10.8.x 
       Postgres on Windows  July 05, 2023
       Postgres on Linux    July 05, 2023

*For Professional Database Administrators, requires advanced RDBMS and advanced Geodatabase experience.

_________________________________________________________________

--10. Docker Container PostgreSQL pg_dump backup

 How to Move the PostgreSQL Enterprise Geodatabase with pg_dump and pg_restore

The simplest way to back up a PostgreSQL database running in Docker is using pg_dump:

docker exec -t postgres-db pg_dump -U myuser mydatabase > backup.sql

To automate your backups, you can create a simple shell script and run it with cron:

#!/bin/bash
# pg_dump_bkp.sh
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
BACKUP_DIR="/tmp/pg_backups"

# Make sure the backup directory exists
mkdir -p $BACKUP_DIR

# Create the backup
docker exec postgres-db pg_dump -U myuser mydatabase | gzip > $BACKUP_DIR/postgres_$TIMESTAMP.sql.gz <-- SQL 
docker exec postgres-db pg_dump -U myuser -F c -b -v mydatabase | gzip > $BACKUP_DIR/postgres_$TIMESTAMP.bkp.gz <-- DUMP

# Remove backups older than 30 days
find $BACKUP_DIR -name "postgres_*.sql.gz" -mtime +30 -delete

Make the script executable and run it:

chmod +x pg_dump_bkp.sh
bash pg_dump_bkp.sh

To fully automate the backup, you can add the script to your crontab to run daily:

0 3 * * * /path/to/pg_dump_bkp.sh

Example:

docker exec -t pg-db-16 pg_dump -U postgres pm > /dockerhome/postgresql/backups/pg-db-16/pg-dump/pg_bkp_pgdb169_pm.sql

docker exec -t pg-db-16 pg_dump -U postgres -F c -b -v pm > /dockerhome/postgresql/backups/pg-db-16/pg-dump/pg_bkp_pgdb169_pm.bkp

cd /dockerhome/postgresql/scripts

cat pg_dump_bkp.sh
----------------
#!/bin/bash
# Usage: ./pg_dump_bkp.sh <container_name> <database_name>

# Check for required arguments
if [ $# -ne 2 ]; then
echo "Usage: $0 <docker_container_name> <database_name>"
exit 1
fi

CONTAINER_NAME="$1"
DATABASE_NAME="$2"
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
BACKUP_DIR="/dockerhome/postgresql/backups/${CONTAINER_NAME}/pg-dump/${DATABASE_NAME}"
#--(Optional): mount a host directory into the container for postgresql backups using -F d (directory option) for large databases
BACKUP_DIR2="/var/lib/postgresql/backup/${DATABASE_NAME}"

# Make sure the backup directory exists
#mkdir -p "$BACKUP_DIR"

# Create the backup
#--docker exec "$CONTAINER_NAME" pg_dump -U postgres -F c -b -v "$DATABASE_NAME" | gzip > "$BACKUP_DIR/pg_bkp_${CONTAINER_NAME}_${DATABASE_NAME}_$TIMESTAMP.bkp.gz"

#--(Optional): mount a host directory into the container for postgresql backups using -F d (directory option) for large databases
docker exec "$CONTAINER_NAME" pg_dump -U postgres -F c -b -v -f "$BACKUP_DIR2/pg_bkp_${CONTAINER_NAME}_${DATABASE_NAME}_$TIMESTAMP.bkp" "$DATABASE_NAME"

gzip "$BACKUP_DIR/pg_bkp_${CONTAINER_NAME}_${DATABASE_NAME}_$TIMESTAMP.bkp"

# Remove backups older than 30 days
find "$BACKUP_DIR" -name "pg_bkp_${CONTAINER_NAME}_${DATABASE_NAME}_*.bkp.gz" -mtime +30 -delete

---------------

chmod +x ./pg_dump_bkp.sh

./pg_dump_bkp.sh pg-db-16 pm

--extract the backup from gzip file
gzip -d $BACKUP_DIR/pg_bkp_pgdb169_pm_$TIMESTAMP.bkp.gz

--The reason you cannot perform a pg_dump -F d (directory format) backup and copy it to the host in one step is due to
--how the directory format works and how Docker handles file I/O.

cd /dockerhome/postgresql/scripts
cat pg_dump_bkp_dir.sh
---------------
#!/bin/bash
# Usage: ./pg_dump_bkp_dir.sh <container_name> <database_name>

# Check if both container name and database name are provided
if [ -z "$1" ] || [ -z "$2" ]; then
echo "Usage: $0 <container_name> <db_name>"
exit 1
fi

# Configuration
CONTAINER_NAME="$1"
DB_NAME="$2"
DB_USER="postgres"
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
CONTAINER_BACKUP_DIR="/var/lib/postgresql/data/backups/pg_bkp_${CONTAINER_NAME}_${DB_NAME}_${TIMESTAMP}"
HOST_BACKUP_DIR="/dockerhome/postgresql/backups/${CONTAINER_NAME}/pg-dump/${DATABASE_NAME}/pg_bkp_${CONTAINER_NAME}_${DB_NAME}_${TIMESTAMP}"

# Step 1: Create backup directory inside container
docker exec -t "$CONTAINER_NAME" mkdir -p "$CONTAINER_BACKUP_DIR"

# Step 2: Run pg_dump in directory format
docker exec -t "$CONTAINER_NAME" pg_dump -U "$DB_USER" -F d -j 16 -b -v -f "$CONTAINER_BACKUP_DIR" "$DB_NAME"

# Step 3: Copy backup from container to host
docker cp "$CONTAINER_NAME":"$CONTAINER_BACKUP_DIR" "$HOST_BACKUP_DIR"

# Step 4: Remove backup directory from container
docker exec -t "$CONTAINER_NAME" rm -rf "$CONTAINER_BACKUP_DIR"

echo "Backup completed and copied to host. Container backup directory cleaned up."
---------------
chmod +x pg_dump_bkp_dir.sh

./pg_dump_bkp_dir.sh pg-db-16 pm

Alternative (Advanced)

If you want to avoid copying, you could mount a host directory into the container using Docker volumes:

docker run -v /host/backup/dir:/container/backup/path ...

Then run pg_dump -F d -f /container/backup/path — this way, the backup is written directly to the host.

Step by step:

Step 1: Create a Host Directory for Backups

Choose or create a directory on your host where you want the backups to be stored:

mkdir -p /dockerhome/postgresql/backups/pg-db-16/pg-dump

Step 2: Run the PostgreSQL Container with a Volume Mount

When starting your PostgreSQL container, mount the host directory into the container using the -v flag:

docker run -d \
--name pg-db-16 \
-e POSTGRES_PASSWORD=yourpassword \
-v /dockerhome/postgresql/backups/pg-db-16/pg-dump:/var/lib/postgresql/backup \
postgres:16

This mounts the host directory to /var/lib/postgresql/backup inside the container.

If your container is already running, you’ll need to recreate it with the volume mount, as Docker doesn’t allow modifying volumes on a running container.

Step 3: Run pg_dump to the Mounted Volume

Now, inside the container, you can run:

docker exec -t pg-db-16 pg_dump -U postgres -F d -j 16 -b -v -f /var/lib/postgresql/backup/pg_bkp_pm pm

This will create a directory-format backup directly in your host’s mounted folder:

/dockerhome/postgresql/backups/pg-db-16/pg-dump/pg_bkp_pm/

cd /dockerhome/postgresql/scripts
cat pg_dump_bkp_dir.sh
----------------
#!/bin/bash
# Usage: ./pg_dump_bkp_dir.sh <container_name> <database_name>

# Check for required arguments
if [ $# -ne 2 ]; then
echo "Usage: $0 <docker_container_name> <database_name>"
exit 1
fi

CONTAINER_NAME="$1"
DATABASE_NAME="$2"
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
BACKUP_DIR="/dockerhome/postgresql/backups/${CONTAINER_NAME}/pg-dump/${DATABASE_NAME}"
BACKUP_FOLDER="pg_bkp_${CONTAINER_NAME}_${DATABASE_NAME}_${TIMESTAMP}_bkp"
# mount a host directory into the container for postgresql backups using -F d (directory option) for large databases
CONTAINER_BACKUP_DIR="/var/lib/postgresql/backup/${DATABASE_NAME}/${BACKUP_FOLDER}"

# Make sure the backup directory exists
#mkdir -p "$BACKUP_DIR"
#mkdir -p "$BACKUP_FOLDER"

# Create the backup

docker exec "$CONTAINER_NAME" pg_dump -U postgres -F d -j 16 -b -w -f "$CONTAINER_BACKUP_DIR" "$DATABASE_NAME"

tar -czvf "$BACKUP_DIR"/"$BACKUP_FOLDER".tar.gz -C "$BACKUP_DIR" "$BACKUP_FOLDER"

rm -rf "$BACKUP_DIR"/"$BACKUP_FOLDER"

# Remove backups older than 30 days
DAYS_TO_KEEP=30
find "$BACKUP_DIR" -name "pg_bkp_${CONTAINER_NAME}_${DATABASE_NAME}_*_bkp.tar.gz" -mtime +$DAYS_TO_KEEP -delete
find $BACKUP_DIR -type d -mtime +$DAYS_TO_KEEP -name "*" -exec rm -rf '{}' ';'

---------------
chmod +x pg_dump_bkp_dir.sh

./pg_dump_bkp_dir.sh pg-db-16 pm

How to extract 
tar -xzvf mydata.tar.gz

_________________________________________________________________
--11. Docker Container PostgreSQL pg_restore

  How to Move the PostgreSQL Enterprise Geodatabase with pg_dump and pg_restore

To restore a PostgreSQL database using pg_restore within a Docker container, you typically follow these steps:

Step-by-Step Instructions

1. Copy the backup file into the container (optional)

If your backup file is on the host machine:

docker cp my_backup.dump my_postgres:/my_backup.dump

2. Run pg_restore inside the container

You can execute pg_restore using docker exec:

docker exec -i my_postgres pg_restore \
   -U postgres \
  -d my_database \
  -v /my_backup.dump

-U postgres: PostgreSQL user

-d my_database: Target database name

-v: Verbose output

/my_backup.dump: Path to the backup file inside the container

If the database doesn't exist yet, you can create it first:

docker exec -i my_postgres createdb -U postgres my_database

Alternative: Pipe from host

If you don’t want to copy the file into the container:

cat my_backup.dump | docker exec -i my_postgres pg_restore \
  -U postgres \
  -d my_database \
  -v

Step-by-step Example:

--Restore each schema, follow this order: public,sde,pm,raster,pmeditor,pmviewer

--Restore public

docker exec -i pg-db-16 pg_restore -U postgres -d pm \
-n public < /dockerhome/postgresql/backups/pg-db-16/pg-dump/pg_bkp_pg-db-16_pm_20250520.bkp

pg_restore: error: could not execute query: ERROR:  
relation "sde.sde_coordinate_systems" does not exist
Command was: 
ALTER TABLE ONLY public.sde_spatial_references
    ADD CONSTRAINT spatial_ref_fk1 FOREIGN KEY (cs_id) 
REFERENCES sde.sde_coordinate_systems(id);

docker exec -it pg-db-16 psql -U postgres -d postgres -p 5432

ALTER TABLE ONLY public.sde_spatial_references
    ADD CONSTRAINT spatial_ref_fk1 FOREIGN KEY (cs_id) 
REFERENCES sde.sde_coordinate_systems(id);

Note: import sde backup then recreate the constraint!!!

--Restore sde

docker exec -i pg-db-16 pg_restore -U postgres -d pm \
-n sde < /dockerhome/postgresql/backups/pg-db-16/pg-dump/pg_bkp_pg-db-16_pm_20250520.bkp

docker exec -it pg-db-16 psql -U postgres -d postgres -p 5432

psql -h ps026300 -p 5437 -U postgres -d postgres
\c pm
ALTER TABLE ONLY public.sde_spatial_references 
ADD CONSTRAINT spatial_ref_fk1 
FOREIGN KEY (cs_id) REFERENCES sde.sde_coordinate_systems(id);

--Restore pm

docker exec -i pg-db-16 pg_restore -U postgres -d pm \
-n pm < /dockerhome/postgresql/backups/pg-db-16/pg-dump/pg_bkp_pg-db-16_pm_20250520.bkp

--Restore raster

docker exec -i pg-db-16 pg_restore -U postgres -d pm \
-n raster < /dockerhome/postgresql/backups/pg-db-16/pg-dump/pg_bkp_pg-db-16_pm_20250520.bkp

--Restore pmeditor

docker exec -i pg-db-16 pg_restore -U postgres -d pm \
-n pmeditor < /dockerhome/postgresql/backups/pg-db-16/pg-dump/pg_bkp_pg-db-16_pm_20250520.bkp

--Restore pmviewer

docker exec -i pg-db-16 pg_restore -U postgres -d pm \
-n pmviewer < /dockerhome/postgresql/backups/pg-db-16/pg-dump/pg_bkp_pg-db-16_pm_20250520.bkp

psql -h ps026300 -p 5437 -U postgres -d postgres

--Update search_path to public,sde otherwise will fail to draw the data
ALTER DATABASE pm SET search_path="$user", public, sde;
GRANT ALL ON DATABASE pm TO public;
GRANT ALL ON DATABASE pm TO postgres;

_________________________________________________________________

--12. Docker Container PostgreSQL database maintenance

Here's a shell script that performs PostgreSQL maintenance (VACUUM, REINDEX, and ANALYZE) inside a Docker container.

It accepts the container name and database name as input arguments and uses a .sql file from the host machine to execute the commands via psql.

Accepts the container name, database name, and SQL file path as arguments.
Saves the output of the maintenance commands to a .log file.
Stores the log file in a logs/ directory on the host machine, creating it if it doesn't exist.

--------------------------------------------
Script: pg_db_maintenance.sh

#!/bin/bash

# Usage: ./pg_maintenance.sh <container_name> <database_name> <path_to_sql_file>

CONTAINER_NAME="$1"
DATABASE_NAME="$2"
SQL_FILE="$3"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
LOG_DIR="./logs"
LOG_FILE="$LOG_DIR/maintenance_${CONTAINER_NAME}_${DATABASE_NAME}_${TIMESTAMP}.log"

# Validate input
if [[ -z "$CONTAINER_NAME" || -z "$DATABASE_NAME" || -z "$SQL_FILE" ]]; then
echo "Usage: $0 <container_name> <database_name> <path_to_sql_file>"
exit 1
fi

# Create logs directory if it doesn't exist
mkdir -p "$LOG_DIR"

# Run the SQL maintenance commands and log the output
echo "Running maintenance on database '$DATABASE_NAME' in container '$CONTAINER_NAME'..."
docker exec -i "$CONTAINER_NAME" psql -U postgres -d "$DATABASE_NAME" < "$SQL_FILE" &> "$LOG_FILE"

echo "Maintenance completed. Log saved to: $LOG_FILE"

--------------------------------------------
Example SQL File: pg_db_maintenance.sql

VACUUM FULL;
REINDEX DATABASE;
ANALYZE;

--------------------------------------------
Replace current_database with your actual database name or use SELECT current_database(); inside the SQL if needed.
--------------------------------------------
How to Use
1. Save the script as pg_maintenance.sh and make it executable:
chmod +x pg_db_maintenance.sh
2. Create your pg_db_maintenance.sql file with the desired commands.
3. Run the script:
./pg_maintenance.sh my_postgres_container my_database /path/to/maintenance.sql
--------------------------------------------

To fully automate the backup, you can add the script to your crontab to run daily:

0 3 * * * /path/to/pg_db_maintenance.sh my_postgres_container my_database /path/to/pg_db_maintenance.sql

------------------------------------------------------------------------------------------------------------------------------------

Examples:

cd /dockerhome/postgresql/scripts

./pg_db_maintenance.sh pg-db-16 agmgdbpg pg_db_maintenance.sql

--Manual Examples:

--A:

docker exec -i pg-db-16 psql -U postgres \
-d agmgdbpg < pg_db_maintenance.sql &> ./logs/maintenance_pg-db-16_agmgdbpg_20250523.log

--B:

docker exec -it pg-db-16 psql -U postgres -d agmgdbpg -p 5432
VACUUM FULL;
REINDEX DATABASE;
ANALYZE;
\quit

_________________________________________________________________