These are PostgreSQL guides specific to Managed Databases:

• Connect to a PostgreSQL Managed Database

• PostgreSQL Extensions

• Migrate a PostgreSQL Database to a Managed Database.

And here are general PostgreSQL guides that aren't specific to Managed Databases:

• An Introduction to PostgreSQL

• Backing Up a PostgreSQL Database (Database Dump)

Connect to a PostgreSQL Managed Data

To connect to a PostgreSQL Managed Database, you need to know a few important details, such as the username, password, and host (or IP). You'll also need a PostgreSQL client. This guide details how to access your database using popular tools.

View connection details

1. Log in to Cloud Manager and select Databases from the left navigation menu.

2. Select your PostgreSQL Managed Database from the list. This opens the detail page for that database cluster.

The Connection Details section contains information and credentials needed for you to connect to your database.

• Username: The default user for all PostgreSQL Managed Databases is linpostgres. This replaces the root user, which is not accessible. Superuser access is not available.

  📘

  The linpostgres user and default postgres database should be used for administrative purposes only, you can create separate users and databases for application purposes. For more information about creating users and databases, see Using PostgreSQL.

• Password: The randomly generated password for your database cluster. If you want, you can reset the root password.
• Host: The fully qualified domain name you can use to reach your database cluster through the public network.
• Private network host: The fully qualified domain name you can use to reach your database cluster through the data center's private network (not a VLAN). Communicating with a Database Cluster over the private network avoids network transfer fees, so it's recommended to use this host string when possible.
• Port: The default port for your database is 5432.
• SSL: This field is set to ENABLED, which means that it is required to use an encrypted TLS/SSL connection.

Under the Connection Details section, there is a Download CA Certificate link, which lets you download the CA (Certificate Authority) certificate. This certificate file can be used if you wish to verify the CA certificate when connecting to the database.

Connect using psql (CLI)

To connect direct to the database from the command-line, you can use the psql tool. This tool is included as part of most PostgreSQL server installations, though you can also install it separately on most operating systems.

1. Make sure the IP address assigned to your system is included within your database's access controls. If not, add it now. You can get this value by managing access controls.

2. Verify that the psql tool is installed on your system by running the following command:

   psql --version
   

   If it is not installed, follow the steps for your operating system under Install psql.

3. Use the psql command below to connect to your database, replacing [host] and [username] with the corresponding values in the Connection Details section.

   psql --host=[host] --username=[username] --password --dbname=postgres
   

   📘

   If you are connecting to the private network host, ensure your Compute Instance is located within that same data center and you have added a Private IPv4 address to that Compute Instance. See Managing IP addresses.

4. Enter your password at the prompt.

Once you are connected successfully, the PostgreSQL prompt appears and you can enter commands to interact with the database. See the psql documentation to learn more about the command and the options available to you.

Install psql

If you do not currently have the psql command-line client installed on your system, follow the instructions in this section to install it through your distribution's repositories.

📘

If you require a specific version of the PostgreSQL client, it's recommended to use the official PostgreSQL repositories instead of your distribution's. Visit PostgreSQL Downloads, select your operating system (and distribution), and follow the corresponding installation instructions.

• CentOS Stream 9 (and 8), CentOS/RHEL 8 (including AlmaLinux 8 and RockyLinux 8):

  sudo dnf install postgresql
  

• CentOS/RHEL 7:

  sudo yum install postgresql
  

• Fedora:

  sudo dnf install postgresql
  

• Ubuntu and Debian:

  sudo apt update
  sudo apt install postgresql-client
  

Connect using pgAdmin (gui)

pgAdmin is an open-source application that provides you with a graphical interface for interacting with your PostgreSQL database. It can be used on a server (through a web browser) or as a stand-alone desktop application. These instructions cover installing and using the desktop application.

1. Download and install pgAdmin from the pgAdmin Downloads page, making sure to select the appropriate file for your local operating system.

2. The first time the application runs, you are prompted to configure a primary password (also called master password) that is used to unlock any saved passwords for database servers you may configure.

3. Next, you are prompted to register a server (configuring a connection to a server). If you do not see this screen, click Object > Register > Server from the main menu.

4. Under the General tab, set a name for your server.

   

5. Under the Connection tab, configure the connection details for this server. To obtain this information, see View Connection Details.

   

6. Under the SSL tab, you can leave SSL Mode as Prefer or explicitly set it to Require. You may also set it to Verify-CA if you wish to verify the CA (Certificate Authority) certificate each time you connect. If you choose this option, download the CA certificate from Cloud Manager and set the Root Certificate field to point to that downloaded file.

7. Once all the settings have been appropriately configured, click the Save button to save the settings and attempt to connect.

   

Connect using DBeaver

DBeaver is free and open source universal database tool for developers and database administrators. DBeaver provides a powerful SQL-editor, administration features, ability to migrate data and schema, monitor database connection sessions, and others.

1. Make sure the IP address assigned to your system is included within your database's access controls. If not, add it now. See Manage Access Controls.

2. Install the DBeaver Community (or Pro) software from the DBeaver Downloads page. Be sure to select the operating system you're using locally.

3. Open DBeaver, click the Database menu dropdown, and select New Connection.

4. The Connect to a database window appears. Select PostgeSQL and click Next to continue.

   

5. In the Main tab, enter the details for your connection, including the Server Host (hostname) Port, and Username. You can optionally store your password by entering your password and clicking the Save password locally button. If you do not store your password, you must enter it manually each time you connect. For security reasons, it's typically recommended not to store your password.

   

6. Click the Test Connection button to check if the connection is successful.

PostgreSQL Extensions

The functionality of PostgreSQL can be enhanced through the use of extensions. The PostgreSQL Managed Database service supports many of these extensions.

Manage extensions

Many extensions can be viewed, installed, and removed directly from the PostgreSQL command-line prompt. To access this prompt, connect to your database using the psql tool. See Connect to a PostgreSQL Database > psql.

View installed extensions

To view the extensions that are currently installed on your PostgreSQL Managed Database, run the \dx command at the prompt. The output should be similar to the following, displaying each extension along with its version number, schema, and a short description.

     Name      | Version |   Schema   |          Description
---------------+---------+------------+--------------------------------
 plpgsql       | 1.0     | pg_catalog | PL/pgSQL procedural language

View available extensions

To see a full list of all the extensions available for your version of PostgreSQL, run the following query. You can also review the List of Available Extensions.

SELECT * FROM pg_available_extensions;

❗️

​Akamai​ doesn't provide superuser access to PostgreSQL Managed Databases. So, some extensions may not function properly or may encounter permissions issues.

Install an extension

To install one of the available extensions on your database, use the CREATE EXTENSION command, replacing [extension_name] with the name of the extension you wish to install.

CREATE EXTENSION IF NOT EXISTS [extension_name];

If this command does not work, the extension may either not be compatible or it may need to be manually enabled on your Managed Database by our team. Review the extensions list.

Remove an extension

To remove an extension, use the DROP EXTENSION command, replacing [extension_name] with the name of the extension you wish to install.

DROP EXTENSION [extension_name];

List of available extensions

The table below lists all of the PostgreSQL extensions that may be supported by our Managed Database service, along with the compatible PostgreSQL versions.

❗️

​Akamai​ doesn't provide superuser access to PostgreSQL Managed Databases. So, some extensions may not function properly or may encounter permissions issues. Extensions that are known not to work, such as adminpack, have been marked as incompatible in the list below. If you encounter an extension that isn't working as expected due to permissions issues, our team can review the extension. Reach out to Support with the extension name, the name of the database for which you wish to use the extension, and your use case.

* This extension may need to be manually enabled by our team. Contact Support with the name of the extension and the name of the Managed Database you wish to enable it on.

If you would like to use an extension that's not currently listed here, contact our Support team and they'll be able to send your feedback to our developers.

Migrate a PostgreSQL Database to a Managed Database

When migrating a database, there are two important terms to keep in mind: the source database and the target database.

• Source database: The original database running on a software, system, or machine that you wish to decommission. This could be PostgreSQL running within your own Linux server, a development database on your local machine, or even a cloud database.
• Target database: The new replacement database that you wish to use. For this guide, the target database will be a Managed Database running on the Akamai cloud computing.

Your individual migration workflow could deviate from the instructions provided here. You may need to consult with your developers or your application's documentation to learn how to perform some of these steps and to gather any best practices. You should also perform the migration on a staging server first and/or during a time when downtime least affects your users and/or business.

Before you begin

• You need to have a cluster created.

• Ensure that the PostgreSQL version on your new Managed Database is equal to or greater than the version on your source database cluster. This guide uses the pg_restore utility, which cannot be used to import data into a PostgreSQL version older than on the source machine.

Export from the source database

Exporting the data from the original database is facilitated through the pg_dump and pg_dumpall utilities. The pg_dump tool saves a copy of a single database as a script file, while the pg_dumpall tool can save all databases as well as any database roles.

1. Export the database roles. Replace [user] with the username for your source database. The default username for many PostgreSQL installations is postgres.

   pg_dumpall --roles-only --username=[user] --file=roles.backup
   

   If you are connecting to a remote database, add --host [host] and --port [port] to the command above, replacing [host] with the host url of your remote database and [port] with the port number.

   📘

   This step requires admin/superuser access to the PostgreSQL cluster. If you do not have this level of access and you still wish to preserve the existing roles, you can manually create the roles on your Managed Database though SQL or the createuser functionality of psql. If you do not do this, all data will be owned by the linpostgres user once you import your data to your Managed Database cluster.

2. Export each database you wish to backup. Replace [database-name] with the name of your database and [user] with the username for your source database. The other options in this command are used to ensure maximum compatibility with PostgreSQL Managed Databases.

   pg_dump -Fd --quote-all-identifiers --verbose --lock-wait-timeout=480000 --no-unlogged-table-data --serializable-deferrable --jobs=1 --dbname [database-name] --username [user] --file database.backup
   

   Again, add --host [host] and --port [port] to the command above if you are connecting to a remote database. You can run this command for each database you wish to export, though you may want to edit the --file option with a custom filename.

Import to the target managed database

Once you've successfully backed up the source database, you can import your data into your Managed Database (the target database). To import the database, this guide covers the pg_restore utility.

1. Import your database roles to your Managed Database. Use the psql command. Replace [host], and [username] with the corresponding values for your Managed Database. You can view connection details to get these values.

   psql --host=[host] --username=[username] --dbname=postgres --file=roles.backup
   

   This assumes your database roles backup file is called roles.backup and is located in your current directory (as per previous steps in this guide). If you used a different filename or path for your backup, replace roles.backup as needed in the above command.

2. Create your database, making sure that your preferred database name doesn't already exist. If it does, you can drop the database before creating it. In the commands below, replace [database-name] with the name of your database and [host] and [username] with the corresponding Connection Details for your Managed Database.

   psql --host=[host] --username=[username] --dbname=postgres --command='DROP DATABASE IF EXISTS [database-name];'
   psql --host=[host] --username=[username] --dbname=postgres --command='CREATE DATABASE [database-name];'
   

   ❗️

   Using DROP DATABASE command results in the deletion of any data stored on that database. If you are replacing an existing database and would like to avoid data loss, make sure you have a backup containing any data you would like to retain before running the command.

3. Import your database file to your newly created database.

   pg_restore --host=[host] --username=[user] --verbose --no-tablespaces --dbname=[database-name] database.backup
   

   This assumes your database backup file is called database.backup and located in your current directory (as per previous steps in this guide). If you used a different filename or path for your backup, replace database.backup as needed in the above command.