Postgres

Basics
#

There are 3 default Postgres databases.

template0
template1 and postgres

postgres is meant as a default database for connections. Not required but a lot of tooling uses it as a default.

Database Operations
#

Create a database

CREATE database test;

Connect to a database

\c

Connecting remotely

Pgcli requires a default database, in this case I’ll choose the template1 database.

pgcli -h pi2 -p 5432 -u root -d template1

List tables
#

\dt

User/role management
#

Listing users

\du

Listing users with description

\du+

Dropping user

DROP USER IF EXISTS dky;

Creating a user

create user dky with encrypted password 'install';

Granting access to user

grant all privileges on database mydb to dky;

Listing users via SQL statement

SELECT usename AS role_name,
  CASE
     WHEN usesuper AND usecreatedb THEN
	   CAST('superuser, create database' AS pg_catalog.text)
     WHEN usesuper THEN
	    CAST('superuser' AS pg_catalog.text)
     WHEN usecreatedb THEN
	    CAST('create database' AS pg_catalog.text)
     ELSE
	    CAST('' AS pg_catalog.text)
  END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name desc;

SQL
#

Creating a table

CREATE TABLE person (
 id int,
 first_name VARCHAR(50),
 last_name VARCHAR(50),
 gender VARCHAR(6),
 date_of_birth TIMESTAMP
);

CREATE TABLE teachers (
 id bigserial,
 first_name varchar(25),
 last_name varchar(50),
 school varchar(50),
 hire_date date,
 salary numeric
);

Selecting distinct

select distinct school from teachers;

select distinct school,salary from teachers;

Filtering rows

select last_name,first_name,school from teachers where last_name = 'Ky';

Backup
#

Dumping and restoring a backup - We are doing this remotely vs locally.

pg_dump -h pi2 -U linky -p 5432 linky_production > linky_production.bak

Basics #

Database Operations #

List tables #

User/role management #

SQL #

Backup #