\Database stuff

• Connect to database docker exec -it nostream-db /bin/bash, then psql -U nostr_ts_relay

1. View users table:
   • select * from users;
2. View events table:
   • select * from events;
3. Count events from a table:
   • select count(*) from events;
   • SELECT count(*) FROM events WHERE event_kind = '1';
4. DB size:
   • select pg_size_pretty(pg_database_size('nostr_ts_relay'));
   • \\l+
5. Show tables of a DB:
   • \\c <db_name>
   • \\dt
6. count amount of pubkeys writing:
   • SELECT COUNT(DISTINCT event_pubkey) FROM events;
7. Show uniques pubs:
   • SELECT DISTINCT event_pubkey FROM events;
8. Show all the events from a given pubkey:
   • SELECT * FROM events WHERE event_pubkey = '\\x_hex_pubkey';
9. Show all the events from a given event kind:
   • SELECT * FROM events WHERE event_kind = '1';
10. Show all the events from a given pubkey and event kind:
    • SELECT * FROM events WHERE event_pubkey = '\\x_hex_pubkey' AND event_kind = '1';
11. Export table to csv:
    • docker exec -it nostream-db psql -U nostr_ts_relay -c "\\copy events TO 'home/db_to_.csv' WITH (FORMAT csv, HEADER true)" && docker cp nostream-db:home/db_to_.csv path/to/directory
12. Backup db with logs for errors
    • docker exec -t nostream-db pg_dump -c -U nostr_ts_relay nostr_ts_relay > ~/backup/dbname.sql 2>> ~/logs/dbname.sql.log