knowledge-base/KB/Linux/Server/PostgreSQL.md

---
creation date: 2022-01-08
tags: [note,linux,postgres,database,postgresql,psql]
---

## Commonly used

```shell
-- list all users
\du

-- list all databases
\l

-- list all (public.) tables in a db
\dt+

-- create db
CREATE DATABASE yourdbname;

-- new user/new db
CREATE USER youruser WITH ENCRYPTED PASSWORD 'yourpass';
GRANT ALL PRIVILEGES ON DATABASE yourdbname TO youruser;

-- existing db
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO youruser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO youruser;
REASSIGN OWNED BY olduser TO newuser;

-- change owner
ALTER DATABASE name OWNER TO youruser;

-- revoke from old user
revoke all privileges on database db from "olduser";

-- rename db
ALTER DATABASE db RENAME TO newdb;

-- drop all owned
DROP OWNED BY <user>

-- revoke and drop
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM <user>;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM <user>;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM <user>;
DROP USER <user>;
```

## Updating major versions

```shell
systemctl start postgresql.service // if not already running
chown postgres:postgres /var/lib/postgres
sudo -i -u postgres
pg_dumpall >> old_backup.sql
exit
systemctl stop postgresql.service
mv /var/lib/postgres/data /var/lib/postgres/olddata
pacman -Syyu
mkdir /var/lib/postgres/data
chown postgres:postgres /var/lib/postgres/data
sudo -i -u postgres
initdb --locale $LANG -E UTF8 -D '/var/lib/postgres/data'
exit
systemctl daemon-reload
systemctl start postgresql.service
sudo -i -u postgres
psql -f old_backup.sql postgres // if postgis is used, the lib has to be accessible before importing
exit
```

## Updating major versions in docker(-compose)

* Backup existing instance:
    
    ```shell
    // host
    docker-compose down
    cp -r <data-dir> <data-dir-bak>
    docker-compose up -d
    docker exec -it <container-name> /bin/bash

    // container
    pg_dumpall >> /old_backup.sql
    exit

    // host
    docker cp <container-name>:/old_backup.sql .
    docker-compose down
    rm -rf <data-dir>
    ```
* Change major version tag of container, e.g. edit `docker-compose`
* Restore old database dump
    ```shell
    // host
    docker-compose up -d
    docker cp old_backup.sql <container-name>:/old_backup.sql
    docker exec -it <container-name> /bin/bash

    // container (leave out -U if chowned in container user is the correct one)
    psql -U <user-access-to-postgres, e.g. postgres> -f old_backup.sql
    rm /old_backup.sql
    exit

    // host
    docker-compose down
    docker-compose up -d
    ```
* Verify applications and delete `<data-dir-bak>` and the dumped `.sql` file