Deploy PostgreSQL database for Creatio
To restore a PostgreSQL database from a backup file, you will need psql and pg_restore utilities. They are located in the PostgreSQL software setup folder.
To restore the database from a backup file:
1.Enter DB connection password in the environment variable:
set PGPASSWORD=pg_password (“export PGPASSWORD=pg_password” – for linux)
•pg_password – user password for connecting to the PostgreSQL server.
2.Create a database where the backup data will be restored:
psql --host pg_server_ip --port pg_server_port --username=pg_user --dbname=pg_dbname –c "CREATE DATABASE pg_dbname WITH OWNER = pg_user ENCODING = 'UTF8' CONNECTION LIMIT = -1"
•pg_server_ip – PostgreSQL server address
•pg_server_port – PostgreSQL server port
•pg_user – user for connecting to the PostgreSQL server
•pg_dbname – name of the PostgreSQL DB where the instructions will be executed
3.If you are using AWS RDS:
•Download the ChangeTypesOwner.sql script.
•In the script, replace the “postgres” value with a valid Postgres username.
•Run the updated ChangeTypesOwner.sql script.
4.Restore the database from the backup file:
pg_restore --host pg_server_ip --port pg_server_port --username=pg_user --dbname=pg_dbname --verbose \\path\to\db.backup
•pg_server_ip – PostgreSQL server address
•pg_server_port – PostgreSQL server port
•pg_user – user for connecting to the PostgreSQL server
•pg_dbname – name of the PostgreSQL DB where the instructions will be executed.
5.Execute type converstion:
psql --host pg_server_ip --port pg_server_port --username=pg_user --dbname=pg_dbname --file=CreateTypeCastsPostgreSql.sql
•pg_server_ip – PostgreSQL server address
•pg_server_port – PostgreSQL server port
•pg_user – user for connecting to the PostgreSQL server
•pg_dbname – name of the PostgreSQL DB where the instructions will be executed
•file – path to the CreateTypeCastsPostgreSql.sql file
Note
If you are deploying 7.15.0 or lower version, use the СreateTypeCastsPostgreSql_7_15_0.sql script.
