Share:

This help topic describes information on how to back up and restore PostgreSQL database. The user "postgres" in the PostgreSQL database owns Repository (Postgres_Rep) and Workschema (Postgres_WS) databases. Along with these databases, the postgres user also owns the sample database.

PostgreSQL database runs on port 6065, but there is a connection pooling software called pgBouncer which is running at port 6075. The pgbouncer.ini file has the mapping between the database names which Datagaps uses in the connection string of the ETL Validator Client and the actual database. You can find the pgbouncer.ini file in the <Install_Directory>\app\Datagaps\ETLValidator\pgbouncer_1.8.1 folder.


Backup

The following script backs up Postgres_Rep, Postgres_WS, and Sample databases. The Postgres_WS workschema contains source data used in creating test cases and therefore the size of the workschema database will be more. However, you can wish to purge the test case data prior to taking a backup. 
@echo on
REM The script sets environment variables helpful for PostgreSQL
@SET PATH="C:\>app\Datagaps\ETLValidator\pgsql\bin";%PATH%
@SET PGUSER=postgres
@SET PGPASSWORD=postgres
@SET PGPORT=6065
@SET PGHOST=localhost
cd\
C: 
cd app\Datagaps\ETLValidator\pgsql\bin
pg_dumpall -h localhost -p 6065 -U postgres -c -o -f "../../../postgresDump.sql"
exit

Restore

The following script restores Postgres_Rep, Postgres_WS, and Sample databases. 

@echo on

REM The script sets environment variables helpful for PostgreSQL

@SET PATH="C:\>app\Datagaps\ETLValidator\postgresql_10.3\bin";%PATH%

@SET PGUSER=postgres

@SET PGPASSWORD=postgres

@SET PGPORT=6065

@SET PGHOST=localhost

cd\

C:

cd "C:\app\Datagaps\ETLValidator\pgsql\bin"

dropdb -U postgres -h localhost --if-exists Postgres_Rep

dropdb -U postgres -h localhost --if-exists Postgres_WS

dropdb -U postgres -h localhost --if-exists Sample

psql -f ../../../postgresDump.sql

TIMEOUT /T 100

exit

Information The backup and restore script exports the data into plain text format. This allows you to use the script into latest versions of postgreSQL.
PreviousNext

© Datagaps. All rights reserved.
Send feedback on this topic to Datagaps Support