Manage a PostgreSQL server

Create a server

This guide describes how to create a PostgreSQL server.

Before you begin

Before you begin, make sure you have enough quotas to create a PostgreSQL cluster.

1. Go to Cloud Management, then Quotas Viewer and select the region.

2. On the Database Service tab, select the number of PostgreSQL clusters you need and specify the reason in the field on the right.

3. Click Send request.

Your request will be processed within fifteen minutes and the number of quotas will be increased.

Create a server

1. In the Cloud menu, choose the Database for PostgreSQL tab and click Create PostgreSQL server.

2. Choose the region where you want to host your PostgreSQL server.

3. Choose the appropriate PostgreSQL version.

4. In Compute, select the server flavor. Optionally, you can set your own settings via text mode to enter specific database management system (DBMS) settings in the window that opens.

5. In Storage, choose the volume type and size.

6. (Optionally) Switch on the Enable High availability toggle in PG configuration to deploy three replicas with the same configuration to ensure that your database remains accessible even in the event of a server failure. If you turn on this feature, select the appropriate replication mode:

• Asynchronous: In asynchronous replication, servers work independently. The primary database sends the changes to the replicas and does not wait for confirmation that the changes have been applied. This mode is suitable for scenarios where data consistency with some level of delay is acceptable, though high performance is crucial.

• Synchronous: In synchronous replication, the primary database must wait for confirmation that the changes have been applied in the replica databases. The changes are replicated in real time or near-real time. It provides full protection against data loss but may have lower performance.

You can enable or disable this feature later.

7. In Network settings, configure the network interface.

Choose Public network and configure the Access control list to limit the access to your server to the specified IP addresses.

Note: If you add 0.0.0.0/0, you will allow unrestricted access to all IP addresses from the Internet. We strongly recommend using this setting only temporarily and exclusively on test servers without any sensitive data.

8. In Authentication, add your username.

9. In Server details, specify the server name and database name.

Note: The database size limit is 100 GB. This limit is for the beta stage only. If you need a more extensive database size during beta—please get in touch with support.

10. Click Create PostgreSQL server in the window on the right-hand side.

The server will be created in a few minutes.

Get your credentials

This guide explains how to obtain the password to start using your Postgres server and how to reset it if you forget it.

1. Go to the Database for PostgreSQL tab and select the server you need.

2. Click Generate credentials.

3. Copy and save the password now. Once you close the window you will not be able to recover it.

Note: If you forget your password, you can only reset it. A new password will be generated, and the previous one will be deactivated.

4. Click OK.

Upgrade the PostgreSQL version

If your current PostgreSQL version is not the latest one, you can upgrade it.

1. Go to the Database for PostgreSQL tab and select the server you need.

2. On the Overview tab, click Update in the General info section.

Note: if you use the latest version, the button will be absent.

3. Select the version you need from the drop-down list and click Update.

Delete a server

1. Go to the Database for PostgreSQL tab and select the server you want to delete.

2. Click Delete server.

3. In the new window, type in Delete and click the Delete server button.

Connect to a PostgreSQL server

This guide explains what tools you can use to access your PostgreSQL server and how to connect using psql and pgAdmin.

Before the beginning

Before you connect to your PostgreSQL server, make sure to do the following:

1. Collect the connection details:

• Endpoint. You can find it in the “Connection details”
• Username. You can find it in the “Connection detais”
• Port. You can find it by copying the connection strings in the “Connection details”
• Password. You should have got it separately after creating a server. If you forgot your password, reset it.

2. Make sure your client computer is in the access control list of the PostgreSQL server. It means that the external IP address of PC which is trying to connect to PostgreSQL should be in allowlist (ACL).

3. Determine what tool you will use to connect to your server.

• psql (Postgres Terminal Monitor): The command-line PostgreSQL client, that allows you to enter, edit, and execute SQL commands directly from your terminal or command prompt.

• Native Frontend Tools (e.g., pgaccess, pgAdmin, ApplixWare): They provide graphical user interfaces (GUIs) for PostgreSQL database management. These tools offer a more user-friendly and visual way to create and manipulate databases.

• Programming Languages (e.g., Perl, Tcl): These languages have supported interfaces for PostgreSQL. You can use these languages to write scripts or applications that connect to PostgreSQL databases programmatically. Some of the languages have convenient and powerful GUI toolkits which can help you construct custom applications. For example, pgaccess, mentioned above, is one of such applications written in Tcl/Tk.

• C Programming with LIBPQ: If you're a C programmer, you can use the LIBPQ subroutine library to write C programs that connect to PostgreSQL databases. This allows you to submit SQL commands from your C program, retrieve results, and manage database interactions programmatically. The detailed information on using LIBPQ can be found in "The PostgreSQL Programmer's Guide."

Connect using the psql client

1. Download the PostgreSQL client from the PostgreSQL website for your operating system in order to install psql.

2. Start psql by typing the following command in your terminal:

      Copy
      
    psql -h hostname -p port -d dbname -U username

Replace hostname, port, dbname, and username with your custom values.

3. Enter the password and press Enter.

For more details, refer to the PostgreSQL official documentation.

Connect using pgAdmin

1. Download and install pgAdmin to your client computer.

2. Launch pgAdmin.

3. On the Dashboard tab, choose Add New Server.

4. In the Create - Server dialog box that appears, type the server’s name in the General tab and click Save.

5. Go to the Connection tab and enter the host (endpoint,) port, username, and password in the relevant fields.

6. Click Save.

7. Locate the server you’ve added in the left-side menu and click it to access. For more details, refer to the pgAdmin official documentation.