Docker installation of Postgresql database and basic operations

# Create a mapping directorymkdir /data/postgresql/data

# Start the containerdocker run -d -p 5432:5432 --restart=always -v /data/postgresql/data:/var/lib/postgresql/data -e POSTGRES_PASSWORD=123456 --name postgres postgres:14

# Modify the configuration file /data/postgresql/data/# Set the time zonetimezone = 'Asia/Shanghai'
# Number of connectionsmax_connections = 1000

# Create a mapping directorymkdir /data/postgresql/data

# Start the containerdocker run -d -p 5432:5432 -v /data/postgresql/data:/var/lib/postgresql/data -e POSTGRES_PASSWORD=123456 --name postgres_master postgres:14

# Modify vi /data/postgresql/data/pg_hba.conf to allow slave copyinghost replication all 192.168.0.12/32 trust
host replication all 193.168.0.13/32 trust

# Modify vi /data/postgresql/data/# Enable archive mode to allow the database to archive WAL (Write-Ahead Logging) log files.archive_mode = on
# Number of connectionsmax_connections = 500
# Set the time zonetimezone = 'Asia/Shanghai'

# Restart the containerdocker restart postgres_master 

# Start the container, note that there is no data volume mount here, because you need to delete the data later to synchronize the master node data.docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=123456 -v /data/postgresql/data:/var/lib/postgresql/data --name postgres_slave1 postgres:14

# Enter the containerdocker exec -it postgres_slave1 bash

# Delete data and synchronize the master node data (note -h is followed by the master node ip, and -U is the user name created by the master node for replication. When you need to enter a password, it is the password for the user name used for replication)rm -rf /var/lib/postgresql/data/* && pg_basebackup -h 192.168.0.11 -p 5432 -U postgres -Fp -Xs -Pv -R -D /var/lib/postgresql/data

# The container will be closed after data synchronization.  Just start againdocker restart postgres_slave1

# Modify file vi /data/postgresql/data/# Configure the IP address and port number of the main library, as well as the username and password for copyingprimary_conninfo = 'host=192.168.0.11 port=5432 user=postgres password=123456'   
# Allow queries during recovery.  This allows query readings to be allowed during stream replication when the library is recovered while performing WAL logs.# Read only by slavehot_standby = on
# Set the target timeline for recovery.  Here, setting to latest means that the slave library will always try to connect to the latest timeline of the main library.recovery_target_timeline = latest
# Must be greater than the number of connections to the master node.  This ensures that the slave library can handle all connection requests sent by the master library.max_connections = 1000
# Set the time zonetimezone = 'Asia/Shanghai'
                         
# Restart the slave librarydocker restart postgres_slave1 

# Verify, execute sql on the hostselect * from pg_stat_replication;

docker run -d -p 9999:5432 --name pgpool \
--env PGPOOL_BACKEND_NODES=0:192.168.0.11:5432,1:192.168.0.12:5432,2:192.168.0.13:5432 \
--env PGPOOL_SR_CHECK_USER=postgres \
--env PGPOOL_SR_CHECK_PASSWORD=123456 \
--env PGPOOL_ENABLE_LDAP=no \
--env PGPOOL_POSTGRES_USERNAME=postgres \
--env PGPOOL_POSTGRES_PASSWORD=123456 \
--env PGPOOL_ADMIN_USERNAME=postgres \
--env PGPOOL_ADMIN_PASSWORD=123456 \
--env PGPOOL_USERNAME=postgres \
--env PGPOOL_PASSWORD=123456 \
--env PGPOOL_AUTHENTICATION_METHOD=scram-sha-256 \
pgpool

-- Create an account
CREATE ROLE admin WITH LOGIN PASSWORD '123456' NOSUPERUSER NOCREATEDB NOCREATEROLE;

-- Give the role/Permissions to access the database by account
GRANT ALL PRIVILEGES ON DATABASE testDb TO admin;

-- Set allschemaAccess permissions
DO
$$
    DECLARE
        schema_name TEXT;
    BEGIN
        FOR schema_name IN
            SELECT nspname
            FROM pg_namespace
            WHERE nspname NOT IN ('pg_catalog', 'information_schema')
            LOOP
                EXECUTE format('GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA %I TO admin;', schema_name);
                EXECUTE format('GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA %I TO admin;', schema_name);
                EXECUTE format('GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA %I TO admin;', schema_name);
            END LOOP;
    END
$$;

-- Unbind allschemaAccess permissions
DO
$$
    DECLARE
        schema_name TEXT;
    BEGIN
        FOR schema_name IN
            SELECT nspname
            FROM pg_namespace
            WHERE nspname NOT IN ('pg_catalog', 'information_schema')
            LOOP
                EXECUTE format('REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA %I FROM admin;', schema_name);
                EXECUTE format('REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA %I FROM admin;', schema_name);
                EXECUTE format('REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA %I FROM admin;', schema_name);
            END LOOP;
    END
$$;


-- Set default permissions，The newly created tables belong to this role/account
DO
$$
    DECLARE
        schema_name TEXT;
    BEGIN
        FOR schema_name IN
            SELECT nspname
            FROM pg_namespace
            WHERE nspname NOT IN ('pg_catalog', 'information_schema')
            LOOP
                EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL ON TABLES TO admin;',
                               schema_name);
                EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL ON SEQUENCES TO admin',
                               schema_name);
                EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL ON FUNCTIONS TO admin',
                               schema_name);
            END LOOP;
    END
$$;

-- Query role/account可以连接的数据库
SELECT datname
FROM pg_database
WHERE has_database_privilege('admin', datname, 'CONNECT');

-- Unbind and connect to other databases
REVOKE CONNECT ON DATABASE postgres FROM PUBLIC;

-- Query the roles bound to the database/user
SELECT datacl
FROM pg_database
WHERE datname = 'testDb';

-- Delete roles/account
DROP OWNED BY admin;