Skip to main content

Import PostgreSQL

Overview​

This guide explains how to export a PostgreSQL database from your current setup and import it into an app.

Dump​

Create a dump of your existing PostgreSQL database using pg_dump:

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

If this database is in an app, 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​

  1. Enable Recovery Mode in the Repair section. This pauses the app while you import data.

App Enable Recovery Mode

  1. Open a Web Terminal using the Terminal button in the Console section.

App Terminal

  1. Upload the dump file using the Upload button.

App Upload

  1. Comment out extension information in the dump file:

# sed -e 's/CREATE EXTENSION/-- CREATE EXTENSION/g' -e 's/COMMENT ON EXTENSION/-- COMMENT ON EXTENSION/g' /tmp/pgdump.sql > /tmp/pgdump_mod.sql

  1. 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"

  1. 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​

Verify the import:

  1. Click the PostgreSQL button at the top of the terminal to paste the connection command.
  2. Press Enter to access the PostgreSQL shell.

Postgresql Shell