title: PostgreSQL
category: Database
time: 1731614342346
---

## Backup & Restore

#### Backup

Backup one database
```
pg_dump DATABASE_NAME > /PATH/TO/DUMPFILE.sql

```

Backup entire cluster
```
pg_dumpall > /PATH/TO/DUMPFILE_ALL.sql
```

#### Restore

Restore one database
```
psql DATABASE_NAME < /PATH/TO/DUMPFILE.sql

```

Restore entire cluster
```
psql -f /PATH/TO/DUMPFILE_ALL.sql postgres
```

## Management

### List Databases
```
psql --list \
    --host HOSTNAME \
    --port PORT \
    --username USER \
    --password
```

### In the `psql` Shell
```
\l  # List databases
\du # List users
\dt # List tables in connected database
\dn # List of schemas

\c DATABSE  # To connect to database
\d TABLE    # To get information about specific table

\q  # Exit the Shell
```


### Set Permission
```
# Alter Database Owner
ALTER DATABASE database OWNER TO username;

# Reassign (Change) Table Owner
REASSIGN OWNED BY avnadmin to mautrix

# Add Gropu to user
GRANT group_role TO role

# Remove Group from User
REVOKE group_role FROM role

# Update user password
ALTER ROLE username WITH PASSWORD 'ExamplePassword';

# Update global permission
ALTER ROLE username WITH 

# Add all permission on database to user
GRANT ALL ON DATABASE database TO username;
GRANT ALL PRIVILEGES ON DATABASE database TO username;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO username;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO username;
```