hh_postgress.txt

## Introduction

## PSQL
```bash
psql -Uadmin -h IP -d postgres -W
```

##### Show databases
```bash
postgres=# \l
                                 List of databases
```

##### Change to database
```bash
postgres=# \c template1
```


##### Show tables

```
postgres=# \dt

```

#####  List of installed extensions

```
postgres=# \dx

```

### Stop connections in a DB and Delete DB and User
```
REVOKE CONNECT ON DATABASE "v6-stocks-s999" FROM PUBLIC;
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'v6-stocks-s999'; DROP DATABASE "v6-stocks-s999"; DROP ROLE "v6-stocks_s999_rw";
```

## Backups
##### Export/Dump
```bash
$ pg_dump postgres -U postgres > postgres.sql
```
##### Restore Dump
```bash
$ psql -U postgres postgres  < postgres.sql

```



## Process / Queries
##### Check all the processes that are running
```sql
SELECT pid, datname, now() - pg_stat_activity.query_start AS duration, state, query
FROM pg_stat_activity
where state='active'
```


##### Identify the PID of the hanging query you want to terminate

```sql
SELECT pg_cancel_backend(PID);
```


##### kill it the hard way,

```sql
SELECT pg_terminate_backend(PID);
```

#### Kill all DB session / connections
```bash
psql -U $PGUSER -p $PGPORT -w -h $PGHOST -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '"$1"';"
```

## Sizes / Tamaños

##### Tamaño de una tabla
```sql
SELECT pg_size_pretty(pg_relation_size('area'));
```


##### Top 5 tablas con mayor tamaño
```sql
SELECT relname AS "relation", pg_size_pretty (pg_total_relation_size (C .oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace)
WHERE nspname NOT IN ('pg_catalog','information_schema') AND C .relkind <> 'i' AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size (C .oid) DESC
LIMIT 5;
```

##### Tamaño de la bbdd
```sql
SELECT pg_size_pretty (pg_database_size ('picking_prod_mad1'));
```


##### Tamaño de cada bbdd
```sql
SELECT pg_database.datname,p g_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;
```


### Tamaño Indices
```sql
SELECT pg_size_pretty (pg_indexes_size('actor'));
```

##### To fetch index fragmentation
```sql
SELECT * 
FROM pgstatindex('fragmented_index');
```


##### Tamaño tablespace
```sql
SELECT pg_size_pretty (pg_tablespace_size ('pg_default'));
```

## VACUUM

##### Full vacuum command
```
  vacuumdb --maintenance-db=postgres --all --verbose --full 
```

##### Analize operation table
```sql
VACUUM ANALYZE public.operation
```

## Autovacuum

##### Enable autovacuum in a table
```sql
ALTER TABLE t_foo SET (autovacuum_enabled = on);
```

##### Disable autovacuum in a table
```sql
ALTER TABLE t_foo SET (autovacuum_enabled = off);
```

## Index
##### Get all the indexes sorted by table
```SELECT
    tablename,
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    schemaname = 'public'
ORDER BY
    tablename,
    indexname;
```

##### Tamaño Indices
```sql
SELECT pg_size_pretty (pg_indexes_size('actor'));
```

### REINDEX

## Permisos

### Ver permisos de un usuario
```SQL
SELECT DISTINCT table_catalog, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'mimercadona' AND privilege_type = 'SELECT'
```

### Grant permisos
```SQL
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mimercadona;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO mimercadona;
```