How to Restore a PostgreSQL Backup on OVH’s Managed Database (Without Superuser Access)

by

OVH’s managed PostgreSQL hosting limits superuser operations like DROP SCHEMA, ALTER OWNER, GRANT, and REVOKE, making a standard backup restore fail. This article outlines a clean workaround using psql.

Problem

When restoring a backup with:

psql "host=your-ovh-host port=1234 dbname=your_db user=your_user password=your_pass" -f dump.sql

You may hit errors like:

ERROR: permission denied for schema public
ERROR: syntax error at or near "1"

Why? Because:

  • DROP SCHEMA is blocked.
  • OWNER TO, GRANT, REVOKE statements are not permitted.
  • You don’t own the schema.

✅ Solution: Manual Table Cleanup + Clean Dump Restore

1. 🔽 Connect and Drop All Tables

First, clean up the database by dropping all tables individually (since schema drop is blocked).

psql "host=your-ovh-host port=1234 dbname=your_db user=your_user password=your_pass"

Then execute:

DO

    \[DECLARE stmt text; BEGIN FOR stmt IN SELECT 'DROP TABLE IF EXISTS "' || tablename || '" CASCADE;' FROM pg_tables WHERE schemaname = 'public' LOOP EXECUTE stmt; END LOOP; END\]

;

💡 This avoids needing superuser rights or schema-level drops.

2. Clean the SQL Dump File

Run the Clean Restore

Now import the dump:

psql "host=your-ovh-host port=1234 dbname=your_db user=your_user password=your_pass" -f clean_dump.sql

This workaround keeps your OVH PostgreSQL restore process safe, reliable, and compliant with their restricted environment.