Postgres

Posted April 14, 2024 ‐ 2 min read

Create User/Role

create user user1 with password 'sUperSecreT';

List Users

\du

List databases

\l

Connect to/Switch database from inside `psql`

\c dbname

This is equivalent to use dbname in mysql

Assign privileges

Make user1 God !!

alter role user1 with Superuser;

But more realistically, we should give this user all the privileges only for specific database

GRANT ALL PRIVILEGES ON DATABASE testapp_dev to user1;

Create Database

As a user with correct privileges

CREATE DATABASE db1 OWNER user1;

Describe a Table

If you have used mysql in the past, then this is similar to desc tablename

\d+ tablename;

Backup

pg_dump -U username dbname -Fc > /path/to/db.custom.format.dump

This option :

uses compression by default
can be used by pg_restore

Restore

$ date;pg_restore -U username -d dbname /path/to/db.custom.format.dump > /tmp/restore.log 2>&1;date

Adding date at the beginning and end gives you rough idea of how long it took.

Connect to `psql` inside a docker container

docker exec -it <tagname/image_name> psql -U postgres (Optionally add -d <db_name> at the end if required.)

Restore DB dump to dockerized Postgres

Get container_id from the docker ps command
Find the volumes using : docker inspect -f '{{ json .Mounts }}' <container_id> | python -m json.tool. Note the Destination from the output.
Copy the DB dump from the local machine to docker volume using : docker cp <localpath> <container_name>:<path_to_volume>
Now we restore the DB using pg_restore command : docker exec my_postgres_1 pg_restore -U postgres -d some_database /path/inside/docker/my_data.dump

Source

Automated password

Use .pgpass file. See details
here
File format: hostname:port:database:username:password
Permissions: chmod 0600 ~/.pgpass

Export the query output to CSV

psql -U username DBname -F , --no-align -c "select * from table" > /path/to/file.csv

Extensions

See list of extensions already installed:

=> \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

To install an extension:

=> CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION
=> \dx
                  List of installed extensions
   Name   | Version |   Schema   |         Description
----------+---------+------------+------------------------------
 pgcrypto | 1.2     | public     | cryptographic functions
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

Originally migrated from my devnotes site