Dockerized PostgreSQL

Copy Database Dump into Dump

      mkdir -p ${HOME}/Documents/pgsql-playground/dump/
# cd <path of your data staging-gbsn dump directory>
cp ${PWD}/*.gz ${HOME}/Documents/pgsql-playground/dump/

Database Dump Import Script

            cd ${HOME}/Documents/pgsql-playground/
tee ./dump/restore_locale >/dev/null <<'EXE'
 #!/bin/sh

cat << SQL | psql -p 5432 -U postgres
-- terminate all the process
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity WHERE datname IN
(
    'academia_policy_staging',
    'academia_audit_staging',
    'academia_quote_staging',
    'academia_tds_staging'
);
SQL

cat << SQL | psql -p 5432 -U postgres
-- drop databases
DROP DATABASE IF EXISTS academia_policy_staging;
DROP DATABASE IF EXISTS academia_audit_staging;
DROP DATABASE IF EXISTS academia_quote_staging;
DROP DATABASE IF EXISTS academia_tds_staging;
SQL

cat << SQL | psql -p 5432 -U postgres
-- database creation
CREATE DATABASE academia_policy_staging;
CREATE DATABASE academia_audit_staging;
CREATE DATABASE academia_quote_staging;
CREATE DATABASE academia_tds_staging;
SQL

gunzip -c academia_policy_staging.sql.gz| psql -p 5432 -U postgres academia_policy_staging
gunzip -c academia_audit_staging.sql.gz | psql -p 5432 -U postgres academia_audit_staging
gunzip -c academia_quote_staging.sql.gz | psql -p 5432 -U postgres academia_quote_staging
gunzip -c academia_tds_staging.sql.gz   | psql -p 5432 -U postgres academia_tds_staging
EXE

Database Schema Init Script

            cd ${HOME}/Documents/pgsql-playground/
 cat <<'DDL' tee ./init.sql >/dev/null
-- create database
CREATE DATABASE academia_policy_staging;
CREATE DATABASE academia_audit_staging;
CREATE DATABASE academia_quote_staging;
CREATE DATABASE academia_tds_staging;

-- create user
CREATE USER chorke WITH ENCRYPTED PASSWORD 'sadaqah!';
ALTER  USER chorke WITH SUPERUSER;

-- grant access
GRANT ALL PRIVILEGES ON DATABASE academia_policy_staging TO chorke;
GRANT ALL PRIVILEGES ON DATABASE academia_audit_staging TO chorke;
GRANT ALL PRIVILEGES ON DATABASE academia_quote_staging TO chorke;
GRANT ALL PRIVILEGES ON DATABASE academia_tds_staging TO chorke;
DDL

Stop Host PostgreSQL Service

sudo systemctl stop   postgresql
sudo systemctl status postgresql

Start Guest PostgreSQL Service

docker run -d --name postgres\
 -e POSTGRES_PASSWORD='sadaqah!'\
 -v ./init.sql:/docker-entrypoint-initdb.d/init.sql\
 -v ./dump:/opt/dump\
 -p 5432:5432\
 postgres:12.14

Import Database Dump

docker exec -it postgres bash
(cd /opt/dump;./restore_locale)
psql -U postgres

Verify Database Dump

docker exec -it postgres psql -U postgres
\c academia_policy_staging
\c academia_quote_staging
\dt
\l

Stop Guest PostgreSQL Service

docker rm postgres -f
rm -rf ./dump/*

Start Host PostgreSQL Service

sudo systemctl start  postgresql
sudo systemctl status postgresql

References

Convention for Database Tables Base36 Encoding and Decoding EclipseLink MapStruct Hibernate Liquibase MyBatis Lombok Locale JPA	PostgreSQL » 12 » pgcrypto Base64 MySQL Jasypt