setup_postgres.md

Setup Postgres

• Run Postgres

wget https://gist.githubusercontent.com/dentropy/e408f86de7261a516af9bb43234ae343/raw/b7c1373bff0152fc59c246e8af0a7f7d48bc340b/postgres2.yml
docker-compose -f postgres2.yml up -d
docker-compose -f postgres2.yml logs -f

• Access Postgres

psql postgresql://postgres:[email protected]:5432/postgres

# or

docker exec -it postgres2 psql -U postgres

• Create Database

CREATE DATABASE discorddata;

\c discorddata

exit;

psql postgresql://postgres:[email protected]:5432/discorddata

exit;

• Create Tables

Set postgres_url postgresql://postgres:[email protected]:5432/discorddata in .env

Check currently running queries

SELECT datname, pid, state, query, age(clock_timestamp(), query_start) AS age 
FROM pg_stat_activity
WHERE state <> 'idle' 
    AND query NOT LIKE '% FROM pg_stat_activity %' 
ORDER BY age;

Reset Postgres

\c postgres
DROP DATABASE discorddata;
CREATE DATABASE discorddata;
\c discorddata
dt

Test Queries

select * from guilds_t;

select * from channels_t;

select * from authors_t;

select * from messages_t limit 100;

select * from message_replies_t limit 100;

select * from reactions_t limit 100;

select * from roles_t limit 100;

select * from mentions_t limit 100;

select * from message_urls_t limit 100;