Importing data into Managed Databases for PostgreSQL
Managed Databases for PostgreSQL offers near-complete PostgreSQL compatibility, and allows you to import or migrate your data from any PostgreSQL-compatible database.
Before you start
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:
- Importing data from an existing PostgreSQL database
- Importing data from an existing PostgreSQL backup
- Importing data from a file (.csv, .txt, etc.)
Importing data from an existing PostgreSQL database
Using pg_dump
Requirements
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).
Downloading and importing data into a Managed Database for PostgreSQL
-
Run the following command to download a local export of your database with
pg_dump
, then enter your password when prompted:pg_dump --no-privileges --no-owner -U {username} -h {host} --port {port} -Fc {databasename} > my-backup
-
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:pg_restore --no-privileges --no-owner --clean --if-exists -U {username} -h {host} --port 5432 -d {databasename} my-backup
-
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.
Using pgAdmin
Requirements
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.
Downloading the backup on your local machine
-
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.
Loading your data in your Managed Database for PostgreSQL
-
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.
-
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.
Using Airbyte
Requirements
To complete the following procedure, you must have:
- Access to Airbyte Cloud or have Airbyte installed on your machine.
Adding your existing database as a source
-
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:
- Host
- Port
- Database Name
- Username
- Password
-
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.
Adding your new Managed Database for PostgreSQL as a destination
-
Click Create a connection.
-
Select Postgres from the list of destinations.
-
Fill in the required information to connect to your existing database:
- Host
- Port
- Database Name
- Username
- Password
-
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.
Using Fivetran
Requirements
To complete the following procedure, you must have:
- A Fivetran account, and be able to connect to the Fivetran console
Connecting Fivetran to your Managed Database for PostgreSQL
-
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:
- Host
- Port
- Database Name
- Username
- Password
-
Select Connect directly as Connection method.
-
Select Detect Changes via XMIN as Update Method (other methods are not supported).
-
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.
Connecting Fivetran to your existing database
-
From 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:
- Host
- Port
- Database Name
- Username
- Password
-
Select Connect directly as Connection method.
-
Select Detect Changes via XMIN as Update Method (other methods are not supported).
-
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.
Using Meltano
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.
Requirements
To complete the following procedure, you must have:
- Installed Meltano
Initializing your Meltano project
-
In a terminal, run the command below to initialize a Meltano project named
migrate-postgresql
:meltano init migrate-postgresql
-
Run the following command to access the newly created directory that contains the project:
cd migrate-postgresql
-
Run the following command to add a PostgreSQL-compatible extractor and loader:
meltano add extractor tap-postgres meltano add loader target-postgres
Configuring the extractor to connect to your existing database
-
Run the command below to configure the connection to your existing database:
meltano config tap-postgres set --interactive
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:
- Database Name (5)
- Host (13)
- Password (14)
- Port (15)
- Username (32)
-
Select the tables you want to load:
meltano select tap-postgres {schema-name}-{table1name} meltano select tap-postgres {schema-name}-{table2name} ...
Configuring the loader to connect to your new Managed Database for PostgreSQL
-
Run the command below to configure the connection to your existing database:
meltano config tap-postgres set --interactive
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:
- Database Name (5)
- Host (13)
- Password (14)
- Port (15)
- Username (32)
Executing the data import and loading
-
Run the following command to execute the data import and loading:
meltano run tap-postgres target-postgres
-
When finished, make sure your data is stored in your new database by connecting to it, and performing a query.
Importing data from an existing PostgreSQL backup
Using pg_restore
Requirements
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:pg_restore --no-privileges --no-owner --clean --if-exists -U {username} -h {host} --port 5432 -d {databasename} my-backup
-
When finished, make sure your data is stored in your new database by connecting to it, and performing a query.
Import Data from a file (.csv, .txt, etc.)
Using psql
Requirements
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).
-
In a terminal, access the folder containing your data file:
cd path/to/my-table.csv
-
Connect to your Managed Database for PostgreSQL using
psql
:psql "postgresql://{username}:{password}@{host}:5432/{databasename}?sslmode=require"
-
Create the table structure corresponding to your file column types:
CREATE TABLE {tablename} (column1name type1, column2name type2, ...);
-
Run the command below to load your data to your Managed Database for PostgreSQL using the psql \copy command:
\copy {tablename} FROM './my-table.csv' WITH DELIMITER ',' CSV;
-
When finished, make sure your data is stored in your new database by connecting to it, and performing a query.