# PostgreSQL

Amverum cloud supports the creation of a managed postgres cluster with one or more running DBMS replicas.
Creating a cluster takes place in a few simple steps:

## Deploying a cluster

1. On the main page, click on the “Create” button.
2. Set the “Project Name”, select “Service Type” “PostgreSQL” and the appropriate tariff. 
![postgresql](../img/postgresql.png)
    ```{eval-rst}
    .. admonition:: Attention
       :class: attention
    
       Stable work is possible at tariffs not lower than “Initial”.
    ```
3. Set PostgreSQL DBMS parameters
    ```{eval-rst}
    .. admonition:: Attention
       :class: attention
    
       The database name and username `postgres` are reserved and cannot be specified.
    ```
   - `Name of the database being created` - the name for the database that will be created in the cluster.
   - `User name` - the name for the user under which the DBMS will be logged in.
   - `User password` - password for the above user, under which the DBMS will be logged in.
   - `DBMS cluster size` - number of DBMS replicas. In this case, one of the replicas will be the master and, due to the peculiarities of the DBMS, write access will be available only through it.
      ```{eval-rst}
      .. admonition:: Important
         :class: warning
    
         Billing occurs separately for each replica according to the selected tariff
      ```
   - `Activate Superuser Access` - activate superuser access with username `postgres`.
   - `Password for Superuser` - password for the user `postgres`, under which it is possible to log into the DBMS with superuser rights.
4. Click the "Finish" button and wait for the cluster to enter the "PostgreSQL running" status.

## Connect to the cluster
To access the database from other Amverum projects, just specify one of three domain names:
- Read/write: `amverum-<username>-cnpg-<project_name>-rw`
- Read-only in hot standby mode: `amverum-<username>-cnpg-<project_name>-ro`
- For reading from any of the deployed nodes: `amverum-<username>-cnpg-<project_name>-r`

To access the database from the Internet, you must create a [domain name](../applications/configuration/network.md) 
with type POSTGRES. The client that will connect to the database must support SSL.

In pgAdmin Desktop it is indicated
 - Host name: `<project_name>-<username>.db-msk0.amverum.tech`
 - SSL mode: `prefer`

### 1. Create an application with a suitable tariff.
```{eval-rst}
.. admonition:: Attention
   :class: attention

   For pgadmin to work, you need a container with a tariff of at least "Starter".
```
### 2. At the “Data Upload” stage, select “Via Git”
![db_create](../img/db_create.png)

### 3. At the "Configuration" stage, select "db" and "pgadmin" and click "Finish"
Deployment uses [image](https://hub.docker.com/r/dpage/pgadmin4) Docker `dpage/pgadmin4:${meta.toolchain.version}`

### 4. Specify the required environment parameters.
In the "Variables" section of the project, you must set the following [environment variables](../applications/configuration/variables.md):
 * Variable `PGADMIN_DEFAULT_EMAIL` - pgAdmin admin email
 * Secret `PGADMIN_DEFAULT_PASSWORD` - password for accessing pgAdmin (has nothing to do with the database password)
    
## Setting up pgAdmin
1. In the "Settings" section of the application, activate [free domain name](../applications/configuration/network.md#free-domain-name-amverum) 
 or [add your own](../applications/configuration/network.md#your-domain-name). 
![db_create](../img/pgadmin_domain.png)
2. Wait for the domain name to be linked and go to it.
3. Enter your pgAdmin login information.
4. On the left, right-click Server->Register->Server...
5. In the window, in the General section, indicate the name for the connected database. In the Connection section we indicate:
    * Host name/address - internal [domain name of the project with postgreSQL](#connection-to-cluster).
    You can find the internal domain name on the "Info" page of the DBMS.    
    * Maintenance database - name of the database (default `postgres`)
    * Username - the user name that was specified at the stage of creating the DBMS (if Superuser was activated, it can be either yours or `postgres`)
    * Password - password of the above user.
   
6. Click the "Save" button
   
```{eval-rst}
.. admonition:: Cunning
   :class: hint

   To save on project consumption with pgAdmin, we recommend pausing it after finishing work.
```

## Backups

### Scheduled backups
The service provides the ability to create free backups on a schedule.
Backups will begin to be created the next day at the hour of their activation. The service stores the last <ins>**three**</ins> backups.

```{eval-rst}
.. admonition:: Important
   :class: warning

   After stopping the cluster, daily backups will be disabled. Don't forget to activate them after starting the cluster.
```

### Click-to-click backups
To create a backup, click on the “Create backup” button and enter its description to distinguish it from others. 
The backup name will be generated automatically.

You can create a maximum of <ins>**three**</ins> backups, they are also free.

![backups](../img/postgresql_backups.png)

## Cluster recovery

```{eval-rst}
.. admonition:: Attention
   :class: attention

   After the cluster is restored, a new service will be created. To connect to the restored cluster, do not forget to change the url in your project. The user and passwords will be restored automatically.
```

You can restore the cluster by clicking on the icon of the required backup.

To successfully restore a cluster at the time of creating a backup, the following conditions must be met:
1. Having at least two successful backups
2. Transactions in the interval between the creation of these backups

For example, there is a backup `A`. To restore the cluster to the moment when backup `A` was created, you need to carry out some transactions (creating a table, adding a record, etc.) and create backup `B`. After successfully creating backup `B`, you will be able to restore the cluster from backup `A`.

If you have only one backup or the last backup was selected, the cluster will be restored to its present state, that is, duplicated.

These restrictions are related to the peculiarities of creating backup copies and restoring data in PostgreSQL.

If the recovery takes more than 20 minutes (the time depends on the size of the database), then contact support.

![restore](../img/postgresql_restore.png)