Importing data into Serverless SQL Database

Serverless SQL Databases offers near-complete PostgreSQL compatibility, and allows you to import 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 Serverless SQL Database 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
  • Using pg_dump
  • Using pgAdmin
• 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

Prerequisites

To complete this procedure, you must have installed PostgreSQL 16 (or newer) with pg_dump and pg_restore (bundled with the default PostgreSQL installation).

Downloading and importing data into a Serverless SQL Database

1. 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
   

   Tip

   You can download specific tables using the -t option:

   pg_dump -t table1name -t table2name --no-privileges --no-owner -U {username} -h {host} --port {port} -Fc {databasename} > my-backup
   

2. When finished, make sure the backup is downloaded on your local machine. The default location is in the current directory, under /my-backup.

3. Run the command below to import data into your Serverless SQL Database using pg_restore. Make sure to replace the placeholders with your Serverless SQL Database connection parameters:

   pg_restore --no-privileges --no-owner --clean --if-exists -U {username} -h {host} --port 5432 -d {databasename} my-backup
   

   Note

   You can find this information on the overview page of your database.

4. Enter the database password when prompted.

   Note

   The password is the IAM secret key corresponding to the IAM user or IAM application you are connecting with.

5. When finished, make sure your data is stored in your new database by connecting to it, and performing a query.

Using pgAdmin

Prerequisites

To complete this procedure, you must have:

• Installed pg_Admin.

• Ensured your PGAdmin version is compatible with the PostgreSQL version of the database you will import your data into. Currently, pgAdmin 7.8 or newer is required to fully work with PostgreSQL 16.

Downloading the backup on your local machine

1. Open pgAdmin.

2. Connect to your existing database server by selecting it in the left-hand menu.

3. In the Databases submenu, connect to the database you want to export by selecting it.

4. In the left-hand menu, right-click the database you want to export, and select Backup.

5. Enter a name for the backup file (e.g. my-backup), and keep the default format and compression options.

6. Click Backup. The backup creation process starts, and you can follow its progress by clicking View processes.

7. 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 Serverless SQL Database

1. Register your new database in pgAdmin. Refer to the How to connect to a Serverless SQL Database documentation page for more information.

2. Connect to your database server by selecting it in the left-hand menu.

3. Select your database in the *Databases submenu.

4. In the left-hand menu, right-click the name of your new database, then select Restore.

5. Enter the file name of your backup (e.g. my-backup), and keep the default options.

6. Access the Data options tab, and enable the Do not save - Owner and Do not save - Privileges options.

7. (Optional) If your new database is not empty, access the Query Options tab, and enable the Clean before restore option.

   Tip

   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).

8. Click Restore. The restore process starts, and you can follow its progress by clicking View processes.

9. 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.

Importing data from an existing PostgreSQL backup

Using pg_restore

Prerequisites

To complete this procedure, you must have:

• Installed PostgreSQL 16 (or newer) with pg_restore (bundled with the default PostgreSQL installation).

• A backup file for your database (named my-backup in the following procedure).

1. In a terminal, access the directory containing the backup file, then run the command below to import data to your Serverless SQL Database using pg_restore. Make sure to replace the placeholders with your Serverless SQL Database connection parameters:

   pg_restore --no-privileges --no-owner --clean --if-exists -U {username} -h {host} --port 5432 -d {databasename} my-backup
   

   Note

   You can find this information on the overview page of your database. The password is the IAM secret Key corresponding to the IAM user or IAM application you are connecting with.

2. 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

Prerequisites

To complete this procedure, you must have:

• Installed PostgreSQL 16 (or newer) with pg_restore (bundled with the default PostgreSQL installation).

• A data file corresponding to a single table (named my-table.csv in the following procedure).

\COPY {tablename} TO './my-table.csv' WITH DELIMITER ',' CSV;

1. In a terminal, access the folder containing your data file:

   cd path/to/my-table.csv
   

2. Connect to your Serverless SQL Database using psql:

   psql "postgresql://{username}:{password}@{host}:5432/{databasename}?sslmode=require"
   

   Note

   You can find this information on the overview page of your database. The password is the IAM secret key corresponding to the IAM user or IAM application you are connecting with.

3. Create the table structure corresponding to your file column types:

   CREATE TABLE {tablename} (column1name type1, column2name type2, ...);
   

4. Run the command below to load your data to your Serverless SQL Database using the psql \copy command:

   \copy {tablename} FROM './my-table.csv' WITH DELIMITER ',' CSV;
   

   Tip

   The PostgreSQL COPY command cannot be used directly, as it requires the source file to be available on the PostgreSQL instance itself.

5. When finished, make sure your data is stored in your new database by connecting to it, and performing a query.