client_encoding
and SEARCH PATH
pqsl
with "-f"
loading modified partitioning
"-a"
to show all
export PG_Owner=<database_owner, e.g. "postgres"> export PG_Archiver=<archiver user, e.g. "archiver" > export PG_Reporter=<reporter user, e.g. "report"> export PG_Archiver_pw='<archiver pw, e.g. "$archive">' export PG_Reporter_pw='<reporter pw, e.g. "$report">' export PGUSER=<schema_owner, e.g. "archiver"> export PGHOST=cbmdcs01 export PGPORT=5432 export PGDATABASE=archive schema=archive; outputdir=$(mktemp -d); outputfile=$(mktemp -p ${outputdir}) ; cat > ${outputfile} << EOF --- --- SET client_encoding = "UTF-8"; --- create database DO $$ BEGIN IF NOT EXISTS( SELECT datname from pg_catalog.pg_database where datname = \'$PGDATABASE\' ) THEN EXECUTE \'CREATE DATABASE $PGDATABASE;\' END IF; END $$; \connect $PGDATABASE; --- create roles / users DO $$ BEGIN IF NOT EXISTS( SELECT usename from pg_user where usename = \'$PG_Archiver\' ) THEN EXECUTE \'CREATE USER $PG_Archiver with PASSWORD \'$PG_Archiver_pw\'\'; EXECUTE \'ALTER USER $PG_Archiver with PASSWORD \'$PG_Archiver_pw\'\'; END IF; IF NOT EXISTS( SELECT usename from pg_user where usename = \'$PG_Reporter\' ) THEN EXECUTE \'CREATE USER $PG_Reporter with PASSWORD \'$PG_Reporter_pw\'\'; EXECUTE \'ALTER USER $PG_Reporter with PASSWORD \'$PG_Reporter_pw\'\'; END IF; END $$; -- create scheme DO $$ BEGIN IF NOT EXISTS( SELECT schema_name FROM information_schema.schemata WHERE schema_name = \'$schema\' ) THEN EXECUTE \'CREATE SCHEMA $schema\'; EXECUTE \'GRANT USAGE on SCHEMA $schema to $PGUSER\'; END IF; END $$; -- SET SEARCH_PATH TO "$schema"; EOF cd ${outputdir} && unset PGDATABASE && psql -U PG_Owner -a -f ${outputfile} && rm -ir ${outputdir} && cd -