---
title: "PostgreSQL® - Tutorial - How to migrate an on-premises database to Cloud Databases"
description: "Learn the procedure to migrate a PostgreSQL® instance running on-premises to OVHcloud Cloud Databases for PostgreSQL®."
url: https://docs.us.ovhcloud.com/en/public-cloud/databases/postgresql-tutorial-migrate-premises-database-cloud
lang: en
---
> For AI agents: the complete documentation index is available at https://docs.us.ovhcloud.com/en/llms.txt, the full documentation bundle is available at https://docs.us.ovhcloud.com/en/llms-full.txt.

# PostgreSQL® - Tutorial - How to migrate an on-premises database to Cloud Databases

**Learn the procedure to migrate a PostgreSQL® instance running on-premises to OVHcloud Cloud Databases for PostgreSQL®.**

 

## Requirements

- A [Public Cloud project](https://us.ovhcloud.com/public-cloud/) in your OVHcloud account
- A PostgreSQL® database running on-premises (the "source" instance)
- A PostgreSQL® database running on OVHcloud Cloud Databases (the "target" instance)
- A PostgreSQL® client that can connect to both database instances, source, and target
- Access to the [OVHcloud Control Panel](https://manager.us.ovhcloud.com/)

These guides can help you meet these requirements:

- [Getting Started with Cloud Databases](https://docs.us.ovhcloud.com/en/public-cloud/databases/getting-started.md)
- [PostgreSQL® - Connect with CLI](https://docs.us.ovhcloud.com/en/public-cloud/databases/postgresql-connect-cli.md)

 

## Considerations

- This document outlines an offline migration path for your database, which means you will have to suspend all the writes from your application for the duration of the migration. Ensure you plan sufficient downtime to carry out all the migration tasks.
- Ensure the source and destination PostgreSQL[®](#go-further) versions match.
- Ensure you have good enough bandwidth between the client machine and both source and destination databases.
- Ensure you choose a [Database plan](https://us.ovhcloud.com/public-cloud/prices/#databases) with appropriate compute, storage, and memory resources.

 

## Procedure

### Step 1: Stop writing to the source database

Ensure client applications stop all write activity on the source database side. Depending on your system, this might entail letting your customers know in advance about unavailability, stopping other upstream applications, or some other administrative, operations, or development tasks.

 

### Step 2: Export the schema

Use the `pg_dump` command to export the database schema to the client machine, as an SQL plaintext file:

```bash
$ pg_dump --file "path/to/dump.sql" --host "<on-prem hostname>" --port "<write port>" \
    --username "postgres" --verbose --format=p --schema-only "database-name"
```

 

### Step 3: Export the data

Use the `pg_dump` command to export the database data to the client machine, in a .tar archive file:

**Warning:**

**NOTE:** This command will automatically apply to the default database. To specify a different database, insert `--dbname=<database name"` into the command below after "--username "postgres".

```bash
$ pg_dump --file "path/to/dump.tar" --host "<on-prem hostname>" --port "<write port>" \
    --no-owner --username "postgres" --verbose --format=t --blobs --encoding "UTF8"
```

Exporting the full dataset may take some time, depending on the size of the database and the available bandwidth.

 

### Step 4: Edit the schema

Since you won't have access to superuser privilege (usually named `postgres`) on the destination database, you need to replace references to that role in the schema dump file with another role, either `avnadmin`, the initial admin user of your managed database, or another user you'd have created beforehand.

Open the schema dump file with a text editor and search for lines such as:

```sql
ALTER TABLE public.<table> OWNER TO postgres;
```

Edit it so that it reads:

```sql
ALTER TABLE public.<table> OWNER TO avnadmin;
```

Then, search the schema dump file for anything that is not compatible with the OVHcloud Public Cloud Databases offer. Review [PostgreSQL® - Capabilities and Limitations](https://docs.us.ovhcloud.com/en/public-cloud/databases/postgresql-capabilities-limitations.md) to learn about what the destination database supports.

Pay particular attention to anything related to extensions, users, roles, and schemas. For example, if you installed any third-party extensions, you'll need to remove them as extensions will be handled differently going forward (check the list of supported extensions here: [PostgreSQL® - Available extensions](https://docs.us.ovhcloud.com/en/public-cloud/databases/postgresql-available-extensions.md)).

 

### Step 5: Import the schema

Use the following psql command to restore the schema on the destination:

```bash
$ psql -v ON_ERROR_STOP=1 -h postgresql-xxxxxxxx.database.cloud.ovh.us -p <port> \
    -d defaultdb -U avnadmin < path/to/the/dump.sql
```

This step should be completed quickly.

 

### Step 6: Verify the schema import

Connect to your database service to check if the schema restore was completed successfully:

```bash
psql "postgres://<username>:<password>@<hostname>:<port>/defaultdb?sslmode=require"
```

Check the table(s):

```sql
defaultdb=> \dt
```

Verify the schema:

```sql
defaultdb=> \d <tablename>
defaultdb=> select * from information_schema.columns;
```

 

### Step 7: Import the data

Use the `pg_restore` command to restore the data:

```bash
$ pg_restore -d defaultdb -h <postgresql-xxxxxxxxx.database.cloud.ovh.us> -p <port> \
     --no-owner -U avnadmin --data-only path/to/the/dump.tar -v
```

As for the data export step, depending on the dataset size and the available bandwidth, the operation might take some time.\
You can monitor the disk and network activity from the \[action]Metrics\[/action] tab of the DB service page in your [OVHcloud Control Panel](https://manager.us.ovhcloud.com/), for example CPU usage and disk usage:

 

### Step 8: Verify the data import

You can check if the data was migrated successfully by querying the destination database, e.g.:

```sql
defaultdb=> select pg_size_pretty(pg_total_relation_size('<tablename>'));
defaultdb=> select count(*) from <tablename>;
```

 

### Step 9: Resume operations using the destination database

Once you verify that the database migration was successful, update client applications to have them connect to the destination database. You can now resume normal operations.

 

## Go further

Browse all the [Databases guides](https://docs.us.ovhcloud.com/en/public-cloud/databases/landing-page-databases.md).

Browse the rest of the [OVHcloud US documentation](https://docs.us.ovhcloud.com/en/index.md) for guides on our other products and services.

Learn more about [Managed Databases](https://us.ovhcloud.com/public-cloud/databases/) on the OVHcloud US website.

OVHcloud Managed Databases and Analytics:

- Grafana® is a registered trademark of Grafana Labs and is used with the permission of Grafana Labs. OVH SAS and its subsidiaries are not affiliated with or endorsed by Grafana Labs.
- Kafka® is a registered trademark of The Apache Software Foundation and has been licensed for use by OVHcloud, who has no affiliation with and is not endorsed by The Apache Software Foundation.
- MongoDB® is a registered trademark of MongoDB, Inc.
- MySQL® is a registered trademark of Oracle and/or its affiliates.
- PostgreSQL® is a registered trademark of the PostgreSQL Community Association of Canada.