Importing PostgreSQL Data From a Remote Server

Ready to upgrade to the only fully managed PostgreSQL hosting solution that gives you full control? Easily migrate your PostgreSQL deployments to ScaleGrid and we'll take those time-consuming management tasks off your plate so you can get back to product.

PostgreSQL Import Prerequisites

Here are the things you'll need to import your PostgreSQL data to ScaleGrid:

• Your remote server name, port, and authentication.
• Your current PostgreSQL version at source should be 9.6.x or 10.9.
• If your existing PostgreSQL server is protected by a firewall, you should be able to modify the firewall settings on the source to allow the nodes of your ScaleGrid PostgreSQL deployment to connect to it.

In this tutorial, we're going to show you how to import into an existing, newly created PostgreSQL deployment at ScaleGrid.

Importing Into A ScaleGrid PostgreSQL Deployment

Before starting your import into your newly created PostgreSQL deployment at ScaleGrid, verify that there is no data in this deployment so that any existing data isn't overwritten. If you'd like to override your existing data in the import, first create a backup to ensure your existing data is preserved.

1. Log into your ScaleGrid account, or start a free 30-day trial.
2. Go to your PostgreSQL dashboard, and select the cluster you'd like to import to.
3. If your source PostgreSQL server is protected by a firewall, you will need to add the IP address of the node which will connect to your existing server to the firewall whitelist. Perform the following additional steps:

4. Next, under the 'Overview' tab, click the 'Import Data' button.

5. Enter your ‘Server name’ and ‘Port’.
6. Enter the ‘Username’ and ‘Password’ of the user that will be used to connect to your PostgreSQL database and execute import operation. Please note:
   -- To import a database, the user must have CONNECT privilege on it.
   -- Only objects on which the user has read privileges can be imported.
   -- If you wish to import existing users from the source database, this user needs to have superuser privileges.
   -- If an extension is enabled on the source, the same will need to be enabled on the destination before import. Please contact [email protected] for more information.
7. Enter the maintenance database - we will attempt to connect to this database for identifying databases which can be imported. This need not be the database you wish to import - it can be any database where the user has CONNECT privileges.
8. If your server has SSL enabled, and the import needs to be done using SSL connection - please check the ‘SSL Enabled’ checkbox.
9. Review the information you’ve entered is correct, and click 'validate'.

10. We will test connectivity to the remote server and do some pre-import validation once you click Validate. If there are errors or warning during these pre-checks, you will need to take corrective actions based on the error or warning displayed. If pre-checks are successful, you will be presented with a list of databases that can be imported.
11. Please select the databases you wish to import.
12. If you have superuser privileges on the source cluster, you will also see a "Import Users" checkbox here. If you select this option, we will import all your existing users to the ScaleGrid cluster - their passwords will remain unchanged.
13. Verify your selection and click on Import.

👍

Your PostgreSQL import has now begun!

Your data will be migrated as soon as the import job is complete, and the time will vary based on the size of your data.

Limitations

• This is a one-time import operation and not a live-sync.
• Permissions can be imported if and only if the users already exist on destination, or you have selected the 'Import Users' option. Otherwise you may see some harmless error messages after import, documenting failures to set the requisite privileges.
• Import does not respect locale and encoding settings on the original. If you have custom locale/encoding settings, please check the PostgreSQL Locale & Encoding FAQ.