# Postgres Commands

#### Access Postgres as SuperUser

Use this terminal command to access the local PostgreSQL instance as the postgres user:

```bash
sudo -u postgres psql
```

#### Setting Postgres (Superuser) Password

Normally, the postgres user (superuser) does NOT have a set password.  
This is because the postgres user normally, is accessing locally, and through sudo.

But if you are wanting the superuser to have a password, you can set it, with this:

```bash
sudo -u postgres psql
ALTER ROLE postgres WITH PASSWORD 'Your$trongPassword';
\q
```

Now, the postgres user can log in, remotely, if the pg\_hba.conf file allows.

#### To List Database Privileges

```sql
SELECT * FROM information_schema. table_privileges
where grantee = 'icore_client';
```

Good reference on how to do admin tasks from the command line: [https://medium.com/coding-blocks/creating-user-database-and-adding-access-on-postgresql-8bfcd2f4a91e](https://medium.com/coding-blocks/creating-user-database-and-adding-access-on-postgresql-8bfcd2f4a91e)

Basically, this:

```bash
// Open a command line session with the postgres engine:
sudo -u postgres psql
// Create a database:
create database mydb;
// Create a user:
create user myuser with encrypted password 'mypass';
// Give the user access to the created database:
grant all privileges on database mydb to myuser;
```

To give a user privilege to create databases, use this:

```sql
ALTER USER postgresclient CREATEDB;
```

To give a user Super User privileges:

```sql
ALTER USER librarian WITH SUPERUSER;
```

#### Restoring Database from Command Line<button aria-describedby=":r3k:-tooltip" aria-hidden="true" class="cc-wf6gg8" data-testid="anchor-button" type="button"><svg class="_1reo15vq _18m915vq _syaz1r31 _lcxvglyw _s7n4yfq0 _vc881r31 _1bsbpxbi _4t3ipxbi" fill="none" role="presentation" viewbox="0 0 16 16"></svg></button>

Taken from here: [https://simplebackups.com/blog/postgresql-pgdump-and-pgrestore-guide-examples/](https://simplebackups.com/blog/postgresql-pgdump-and-pgrestore-guide-examples/)

Here’s a quick command to restore a database from a backup file:

```bash
pg_restore -U postgresclient -Ft -C -d dbProjectControls < /path-to-file/backupfilename.tar
```

Login and connect to a database:

```bash
// Do this to open a "postgres-#" session...
sudo -u postgres psql

// List databases with...
\l

// Switch databases with...
\c databasename
```

#### Admin Tasks<button aria-hidden="true" class="cc-wf6gg8" data-testid="anchor-button" type="button"><svg class="_1reo15vq _18m915vq _syaz1r31 _lcxvglyw _s7n4yfq0 _vc881r31 _1bsbpxbi _4t3ipxbi" fill="none" role="presentation" viewbox="0 0 16 16"></svg></button>

Good list of admin tasks: [https://chartio.com/resources/tutorials/how-to-list-databases-and-tables-in-postgresql-using-psql/](https://chartio.com/resources/tutorials/how-to-list-databases-and-tables-in-postgresql-using-psql/)

Another good reference to digest: [https://hasura.io/blog/top-psql-commands-and-flags-you-need-to-know-postgresql/](https://hasura.io/blog/top-psql-commands-and-flags-you-need-to-know-postgresql/)