Jeffrey Morgan

Postgres

Management

  • How to start PostgreSQL 

    sudo systemctl start postgresql

  • How to stop PostgreSQL 

    sudo systemctl stop postgresql

  • How to restart PostgreSQL 

    sudo systemctl restart postgresql

  • Where are the PostgreSQL configuration files? 

    /var/lib/pgsql/data/postgresql.conf
/var/lib/pgsql/data/pg_hba.conf

Data

  • How to show where the PostgreSQL data is stored 

    SHOW data_directory;

  • How to get the human readable size of a PostgreSQL database

    SELECT pg_size_pretty(pg_database_size(DATABASE_NAME));

  • How to get the human readable size of a PostgreSQL table

    SELECT pg_size_pretty(pg_total_relation_size(TABLE_NAME));

Commands

  • How to connect to a PostgreSQL database 

    \connect DATABASE_NAME
\c DATABASE_NAME

  • How to show the current connection information 

    \conninfo

  • How to list the databases in PostgreSQL 

    \list
\l

  • How to list the tables in the current PostgreSQL database 

    \dt

  • How to show the structure of a PostgreSQL database table 

    \d TABLE_NAME

  • How to show more information about the structure of a PostgreSQL database table 

    \d+ TABLE_NAME

  • How to list the schemas in a PostgreSQL database 

    \dn

  • How to list the users in a PostgreSQL database 

    \du

  • How to show the current user in a PostgreSQL database 

    SELECT current_user;

Databases

  • How to create a PostgreSQL database 

    CREATE DATABASE database_name;

Importing

  • How to import SQL into a PostgreSQL database 

    psql -v ON_ERROR_STOP=1 DATABASE_NAME < INPUT.sql

Users

  • How to create a PostgreSQL database user on the command line

    createuser –interactive

  • How to create a PostgreSQL database user

    CREATE USER ‘USERNAME’@’localhost’ IDENTIFIED BY ‘PASSWORD’;

  • How to grant privileges to a user on a PostgreSQL database

    GRANT ALL PRIVILEGES ON * . * TO ‘USERNAME’@’localhost’;

  • How to change the password of a user in a PostgreSQL database

    ALTER USER ‘username’@’localhost’ IDENTIFIED BY ‘NEW PASSWORD’;

    ALTER USER user_name WITH PASSWORD ‘NEW PASSWORD’;

  • How to grant a privilege to create a database to a PostgreSQL database user

    ALTER USER user_name CREATEDB;

  • How to grant all privileges to a user on a PostgreSQL database

    GRANT ALL PRIVILEGES ON DATABASE database_name TO user_name;

  • How to grant all privileges to a user on all tables in a PostgreSQL database

    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user_name;

  • How to revoke all privileges to a user on all tables in a PostgreSQL database

    REVOKE ALL ON ALL TABLES IN SCHEMA public FROM user_name;

Roles

  • How to create a role in a PostgreSQL database

    CREATE ROLE role_name;

  • How to create a role in a PostgreSQL database with privileges

    CREATE ROLE role_name WITH CREATEDB CREATEROLE LOGIN;

  • How to add privileges to a role in a PostgreSQL database

    ALTER ROLE role_name CREATEDB CREATEROLE LOGIN;

Indexes

  • How to list the indexes in a PostgreSQL database

    SELECT indexname, indexdef FROM pg_indexes WHERE tablename = ‘TABLE_NAME’;

Extensions

  • How to check if an extension has been added to a PostgreSQL database 

    SELECT EXISTS (
    SELECT 1
    FROM pg_extension
    WHERE extname = 'EXTENSION_NAME'
);