---
# System prepended metadata

title: "\U0001F418 PostgreSQL"
tags: ["\U0001F6E2 db"]

---

---
tags: 🛢 db
---

<style>
    
    h1 {
        background-image: linear-gradient( rgba(0, 0, 0, 0.25), rgba(0, 0, 0, 0.5) ), url(https://www.postgresql.org/media/img/misc/banner.jpg);
        background-size: cover;
        padding: 25px;
        color: white;
        font-size: 50px !important;
        text-align: center;
    }
    
    .alert-success {
        background: white;
        border: solid 2px black;
        color: black;
    }
    
    .alert-success strong {
        font-size: 18px;
        color: black;
    }
    
    .alert-info {
        background: white;
        border: 2px solid royalblue !important;
        border-left: 7px solid royalblue !important;
        border-radius: 0;
    }
    
    .alert-info strong:first-of-type {
        color: royalblue !important;
        font-size: 18px
    }
    
    h2 {
        padding: 10px;
        padding-left: 20px;
        height: 55px;
        max-height: 55px;
        background: black;
        color: white;
        line-height: 50px;
        font-size: 25px !important;
        font-weight: 100;
        margin-top: 50px !important;
    }
    
    div#doc pre {
        border: 1px solid black !important;
        border-left: 5px solid black !important;
        margin-top: 10px;
        margin-bottom: 10px;
        box-shadow: rgba(50, 50, 93, 0.25) 0px 6px 12px -2px, rgba(0, 0, 0, 0.3) 0px 3px 7px -3px;
        background: white;
    }
    
    div#doc pre code {
        font-family: "Cascadia Code", monospaced;
        border: none;

    }
    
    div#doc code {
        border: 1px solid black;
    }
    
    div#doc pre code span[class$="keyword"] {
        font-weight: bold;
    }
</style>

# 🐘 PostgreSQL

:::success
:link: **Useful Links**
- ++PostgreSQL 14 Documentation++: https://www.postgresql.org/docs/14/index.html
- ++Postgres Guide++: https://www.postgresguide.com/
- ++Postgres Tutorial++: https://www.postgresqltutorial.com/
:::

:::success
:::spoiler **Table of contents**
[ToC]
:::

## Installation

- **CentOS**:

```bash
sudo yum install postgresql-server postgresql-contrib
```

### Initialize database

```bash
sudo postgresql-setup initdb
```

- Then, run the service:
    ```bash
    sudo systemctl start postgresql
    sudo systemctl enable postgresql
    ```
    
:::info
💡 **For more information:** 
- https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-centos-7
:::

## Using `psql` tool 

:::info
:information_source: **Further Reading**
For more information of commands in using `psql`, using `\?` in psql console 
- https://www.postgresqltutorial.com/psql-commands/
- https://www.postgresql.org/docs/13/app-psql.html
:::

### Importants setting files

- `postgresql.conf`:
    ```sql
    SHOW config_file;
    ```
    - Usually located: `/var/lib/pgsql/data/postgresql.conf`
- `pg_hba.conf`:
    ```sql
    SHOW hba_file;
    ```
    - Usually located: `/var/lib/pgsql/data/pg_hba.conf`

### Login into postgres

### Common shortcuts

| Action | Command |
| :- | :-: |
| Quit | `\q` |
| Show all databases | `\l` |
| Connect to database | `\c $db` |
| List all roles | `\du` |
| List all tables | `\d` |

## Common tasks using SQL

### Create DB

```SQL
CREATE DATABASE DatabaseName;
```

### Create user

1. Create user

```sql!
CREATE USER username WITH ENCRYPTED PASSWORD 'password';
```

2. Create role

```sql
CREATE ROLE $roleName WITH $privileges ;
```

- **Eg**:
    ```sql
    CREATE ROLE roleAdmin WITH LOGIN SUPERUSER;
    ```
    
3. Grant role for created user

```sql!
GRANT ALL PRIVILEGES ON DATABASE $databaseName TO $userName;
```

## Common issues and solutions

### 1. Successfully Ping to AWS EC2 but cannot send TCP to Postgres

1. In the `postgresql.conf` file, add this line:
    ```python
    listen_addresses = '*'
    ```
2. In the `pg_hba.conf` file, add these lines:
    ```python
    host    all             all             0.0.0.0/0            md5
    ```
    
### 2. Logging in after installation

#### Reproduce

```bash!
$ psql -U postgres --no-password

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "postgres"
```

#### How-to-fix

1. `nvim /etc/postgresql/15/pg_hba.conf`
2. change from this
    ![image](https://hackmd.io/_uploads/S1MheDyyA.png)
    to this
    ![image](https://hackmd.io/_uploads/ryepewkkR.png)
3. then reload: 
    ```
    psql -U postgres -W -h localhost
    ```
    or
    ```
    sudo -u postgres psql postgres
    ```
    
### 3. Open new port 5432 on a cluster and make postgres accept incoming connections

1. **Open a new port on linux machine**

    - Before all, you need to ensure that 5432 port is still closed
        ```bash
        netstat -lntu
        ```
        > ```
        > Active Internet connections (only servers)
        > Proto Recv-Q Send-Q Local Address    Foreign Address  State
        > tcp        0      0 127.0.0.1:5432   0.0.0.0:*        LISTEN
        > tcp        0      0 127.0.0.1:27017  0.0.0.0:*        LISTEN
        > tcp        0      0 127.0.0.1:6379   0.0.0.0:*        LISTEN
        > tcp        0      0 127.0.0.53:53    0.0.0.0:*        LISTEN
        > tcp        0      0 0.0.0.0:22       0.0.0.0:*        LISTEN
        > tcp6       0      0 ::1:5432         :::*             LISTEN
        > tcp6       0      0 ::1:6379         :::*             LISTEN
        > tcp6       0      0 :::22            :::*             LISTEN
        > udp        0      0 127.0.0.53:53    0.0.0.0:*        LISTEN
        > ```

    - Then, you open a port using `iptables`
        ```bash
        iptables -A INPUT -p tcp --dport 5432 -j ACCEPT
        ```
        
2. Edit file `postgresql.conf`:
    - Change line `60` to: `listen_addresses = '*'`
3. Edit file `pg_hba.conf`:
    - Change line `97` to: `host all all 0.0.0.0/0`
4. Restart postgres server to apply the changes:
    ```bash
    sudo pg_ctlcluster 15 main restart
    sudo pg_lsclusters
    ```