Skip to main content

Import PostgreSQL

Overview​

Export a PostgreSQL database from your current setup and import it into a Cloudron 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 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​

  1. Enable Recovery Mode in the Repair section. This pauses the app while you import data.
  1. Open a Web Terminal using the Terminal button in the Console section.
  1. Upload the dump file using the Upload button.
  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.