Managed Databases for PostgreSQL offers near-complete PostgreSQL compatibility, and allows you to import your data from any PostgreSQL-compatible database.
The methods listed below are best suited for database sizes below 100 GB (approx.). Above this size, we recommend you use dedicated tooling to import your data, as your local storage size and bandwidth will create bottlenecks, and the operation may last several hours.
To complete the actions presented below, you must have:
A Scaleway account logged into the console
Owner status or IAM permissions allowing you to perform actions in the intended Organization
Created a target Managed Database for PostgreSQL to import your data into.
Checked that the target database you are importing data into is empty, or contains data you can overwrite.
The import procedure depends on your data source:
To complete this procedure, you must have installed PostgreSQL in a version compatible with your target Managed PostgreSQL, with pg_dump and pg_restore (bundled with the default PostgreSQL installation).
Run the following command to download a local export of your database with pg_dump, then enter your password when prompted:
You can download specific tables using the -t option:
When finished, make sure the backup is downloaded on your local machine. The default location is in the current directory, under /my-backup.
Run the command below to import data into your Managed Database for PostgreSQL using pg_restore. Make sure to replace the placeholders with your Managed Database for PostgreSQL connection parameters:
You can find this information on the overview page of your database.
Enter the database password you defined upon Database Instance creation when prompted.
When finished, make sure your data is stored in your new database by connecting to it, and performing a query.
To complete the following procedure, you must have:
Installed pgAdmin.
Ensured your pgAdmin version is compatible with the PostgreSQL version of the database you will import your data into. Refer to the official pgAdmin documentation to check the compatibility.
Open pgAdmin.
Connect to your existing database server by selecting it in the left-hand menu.
In the Databases submenu, connect to the database you want to export by selecting it.
Right-click the database you want to export in the left-hand menu, and select Backup.
Enter a name for the backup file (e.g. my-backup), and keep the default format and compression options.
Click Backup. The backup creation process starts, and you can follow its progress by clicking View processes.
When finished, make sure your backup is downloaded on your local machine. By default, the backup location is Users/{username}/my-backup on MacOS.
Register your new database in pgAdmin. Refer to the How to connect to a Managed Database for PostgreSQL documentation page for more information.
Connect to your database server by selecting it in the left-hand menu.
Select your database in the Databases submenu.
Right-click the database you want to export in the left-hand menu, then select Restore.
Enter the file name of your backup (e.g. my-backup), and keep the default options.
Access the Data options tab, and enable the Do not save - Owner and Do not save - Privileges options.
(Optional) If your new database is not empty, access the Query Options tab, and enable the Clean before restore option.
This option ensures that your database is emptied before loading data to avoid potential conflicts with existing data (such as table names or primary key constraints).
Click Restore. The restore process starts, and you can follow its progress by clicking View processes.
When finished, make sure your data is stored in your new database by connecting to it, and performing a query.
If the process fails and some data was already partly transferred, we suggest that you activate the Clean before restore option in the Data options tab to remove partly transferred data.
To complete the following procedure, you must have:
To avoid any statement timeout errors with Managed Database for PostgreSQL (e.g. ERROR: canceling statement due to statement timeout), we recommend you set up the PostgreSQL parameter statement_timeout to 1200000 (20 minutes), as recommended by Airbyte for other PostgreSQL-compatible data sources. You can modify this parameter in your database overview in the Scaleway console, by clicking More, then Advanced Settings.
From the Airbyte user interface, click Sources.
Select Postgres from the list of sources.
Fill in the required information to connect to your existing database:
You can find this information on the overview page of your database. These are the username and password that you defined upon Database Instance creation.
Select an SSL Mode and an SSH Tunnel Method.
Select Detect Changes with Xmin System Column as Update Method (other methods are not supported).
Click Set up source.
Click Create a connection.
Select Postgres from the list of destinations.
Fill in the required information to connect to your existing database:
You can find this information on the overview page of your database. These are the username and password that you defined upon Database Instance creation.
Select an SSL Mode and an SSH Tunnel Method.
Click Set up destination.
Select the streams (tables) you want to load in your new Managed Database for PostgreSQL, then click Next.
Edit the remaining options if required, then click Finish & Sync.
When finished, make sure your data is stored in your new database by connecting to it, and performing a query.
This procedure does not currently work with Managed Databases for PostgreSQL. However, you can import your backup using Managed Database for PostgreSQL and MySQL.
To complete the following procedure, you must have:
From the Fivetran user interface, click Add destination in the destinations list.
Enter a name for the destination (e.g. my-new-database), then click Add.
Select PostgreSQL, then click Select.
Fill in the required information to connect to your existing database:
You can find this information on the overview page of your database. These are the username and password that you defined upon Database Instance creation.
Select Connect directly as Connection method.
Select Detect Changes via XMIN as Update Method (other methods are not supported).
If you have set up IP address access restrictions from your IAM policies, copy the addresses from Fivetran IPs and edit your IAM policies to whitelist them.
Click Save & Continue.
Select the certificate in the list corresponding to CN=sdb.{region}.scw.cloud, check its validity, and confirm your selection by clicking Confirm.
You can ensure the certificate is fully valid only if all of the following checks are true:
sdb.{region}.scw.cloudsdb.{region}.scw.cloudFrom the Fivetran user interface, click Add connector in the connectors list.
Select your destination (for instance my-new-database), then click Select.
Select PostgreSQL, then click Set up.
Fill in the required information to connect to your existing database:
You can find this information on the overview page of your database. These are the username and password that you defined upon Database Instance creation.
Select Connect directly as Connection method.
Select Detect Changes via XMIN as Update Method (other methods are not supported).
If you have set up IP address access restrictions from your IAM policies, copy the addresses from Fivetran IPs and edit your IAM policies to whitelist them.
Click Save & Continue.
When the database schemas and tables display, select the one you want to import, then click Save & Continue.
Select your preferred way of synchronizing future schemas changes and then click Save & Continue.
Click Start Initial Sync.
When finished, make sure your data is stored in your new database by connecting to it, and performing a query.
This procedure does not currently work with Managed Databases for PostgreSQL. However, you can import your backup using Managed Database for PostgreSQL and MySQL.
Tools such as Meltano are better suited to load data only, without keeping constraints on column types or relations. To load all PostgreSQL objects (such as table constraints, users, etc.), we recommend using other tools such as pg_dump and pg_restore.
To complete the following procedure, you must have:
In a terminal, run the command below to initialize a Meltano project named migrate-postgresql:
Run the following command to access the newly created directory that contains the project:
Run the following command to add a PostgreSQL-compatible extractor and loader:
Run the command below to configure the connection to your existing database:
A list of settings displays, with a number associated with each parameter.
Fill in the required information below by entering the number associated with each parameter:
Select the tables you want to load:
For instance meltano select tap-postgres public-cities if you are importing the table cities from the schema public.
You can check the selected tables and columns with meltano select tap-postgres --list --all.
Run the command below to configure the connection to your existing database:
A list of settings displays, with a number associated with each parameter.
Fill in the required information below by entering the number associated with each parameter:
You can find this information on the overview page of your database. These are the username and password that you defined upon Database Instance creation.
Run the following command to execute the data import and loading:
When finished, make sure your data is stored in your new database by connecting to it, and performing a query.
By default, the tap-postgres extractor loads data in the tap_postgres schema, rather than the public schema. You can find your loaded tables with the following command:
To complete the following procedure, you must have:
Installed PostgreSQL 15 (or older) with pg_restore (bundled with the default PostgreSQL installation).
A backup file for your database (named my-backup in the following procedure).
In a terminal, access the directory containing the backup file, then run the command below to import data to your Managed Database for PostgreSQL using pg_restore. Make sure to replace the placeholders with your Managed Database for PostgreSQL connection parameters:
You can find this information on the overview page of your database. These are the username and password that you defined upon Database Instance creation.
When finished, make sure your data is stored in your new database by connecting to it, and performing a query.
To complete the following procedure, you must have:
Installed PostgreSQL in a version compatible with your target Managed PostgreSQL, with pg_restore (bundled with the default PostgreSQL installation).
A data file corresponding to a single table (named named my-table.csv in the following procedure).
You can create a .csv file from an existing PostgreSQL table with the COPY TO command, as shown below:
In a terminal, access the folder containing your data file:
Connect to your Managed Database for PostgreSQL using psql:
You can find this information on the overview page of your database. These are the username and password that you defined upon Database Instance creation.
Create the table structure corresponding to your file column types:
Run the command below to load your data to your Managed Database for PostgreSQL using the psql \copy command:
The PostgreSQL COPY command cannot be used directly, as it requires the source file to be available on the PostgreSQL instance itself.
When finished, make sure your data is stored in your new database by connecting to it, and performing a query.