Basic Postgres Commands

Switch to the postgres Linux user account

sudo -i -u postgres

where postgres can be the name for any Linux user.

Alternatively, run the psql command directly from your regular account:

sudo -u postgres psql

Create a new user/role (e.g. “ibrathesheriff”) with a password

CREATE ROLE ibrathesheriff WITH LOGIN PASSWORD 'your_strong_password';

To grant the user the ability to create databases:

CREATE ROLE ibrathesheriff WITH LOGIN PASSWORD 'your_strong_password' CREATEDB;

Create a new database (e.g., “ibrathesheriffdb”) and assign ownership:

CREATE DATABASE ibrathesheriffdb OWNER ibrathesheriff;

Grant all privileges on the new database to the new user:

GRANT ALL PRIVILEGES ON DATABASE ibrathesheriffdb TO ibrathesheriffdb;

Access the PostgreSQL prompt (psql)

psql

List databases from PostgreSQL prompt

\l

Connect to a database

\c <database-name>

For example,

\c ibrathesheriffdb

View the tables of a database

\dt

View the column properties of a table

\d <table-name>

To quit the PostgreSQL prompt

\q

Issues

I was trying to run a drizzle migration script and ran into password authentication issues. So to test manually I ran the following command:

psql -U postgres -h localhost -d nervebird

And the following error occurred:

psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL:  password authentication failed for user "postgres"
connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL:  password authentication failed for user "postgres"

I ran the following command to open the PostgreSQL prompt:

sudo -u postgres psql

Then to change the password:

ALTER USER postgres WITH PASSWORD 'your_strong_password;