Skip to content

Import PostgresSQL

Overview

In this guide, we will see how to export a PostgreSQL database from your current setup and import it into the PostgreSQL database of a Cloudron app.

Dump

The first step is to create a dump of your existing PostgreSQL database. This can be done using pgdump:

$ PGPASSWORD=password pg_dump --no-owner --no-privileges --username=username --host=myserver databasename > pgdump.sql

If this database is on a Cloudron, you can use the following command:

# PGPASSWORD=${CLOUDRON_POSTGRESQL_PASSWORD} pg_dump --no-owner --no-privileges --username=${CLOUDRON_POSTGRESQL_USERNAME} --host=${CLOUDRON_POSTGRESQL_HOST} ${CLOUDRON_POSTGRESQL_DATABASE} > /tmp/pgdump.sql

Import

  • After install, enable 'Recovery Mode'in the Repair section. This will ensure that the app is paused and not actively using the database when you are importing.

  • Open a Web Terminal by using the Terminal button in the Console section.

  • Upload the dump file using the Upload button.

  • The dump file might contain extension information that needs to be first commented out.
# sed -e 's/CREATE EXTENSION/-- CREATE EXTENSION/g' -e 's/COMMENT ON EXTENSION/-- COMMENT ON EXTENSION/g' /tmp/pgdump.sql > /tmp/pgdump_mod.sql
  • Clear the existing database
# PGPASSWORD=${CLOUDRON_POSTGRESQL_PASSWORD} psql -h ${CLOUDRON_POSTGRESQL_HOST} -p ${CLOUDRON_POSTGRESQL_PORT} -U ${CLOUDRON_POSTGRESQL_USERNAME} -d ${CLOUDRON_POSTGRESQL_DATABASE} -c "DROP SCHEMA public CASCADE; CREATE SCHEMA public"
  • Finally, import the dump using the psql command:
# PGPASSWORD=${CLOUDRON_POSTGRESQL_PASSWORD} psql -h ${CLOUDRON_POSTGRESQL_HOST} -p ${CLOUDRON_POSTGRESQL_PORT} -U ${CLOUDRON_POSTGRESQL_USERNAME} -d ${CLOUDRON_POSTGRESQL_DATABASE} --set ON_ERROR_STOP=on --file=/tmp/pgdump_mod.sql

Verify

Click the PostgreSQL button on top of the terminal to paste the command line required to access the PostgreSQL database. You can now press enter/return to get the PostgreSQL shell.