---
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
to this
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
```
Sign in with Wallet
Connect another wallet