Using PostgreSQL

Connect to a database through a bastion

SSH\_PRIVATE\_KEY=~/.ssh/id_rsa

RDS\_DATABASE\_HOST=opszero-database.aasdasd.us-east-1.rds.amazonaws.com
RDS\_DATABASE\_PORT=5432
RDS\_DATABASE\_USERNAME=postgres
RDS\_DATABASE\_PASSWORD=postgres
RDS\_DATABASE\_DB=postgres_development

BASTION\_USERNAME=ubuntu
BASTION\_HOST=137.32.32.83

ssh -i ${SSH\_PRIVATE\_KEY} -f -N -L ${RDS\_DATABASE\_PORT}:${RDS\_DATABASE\_HOST}:${RDS\_DATABASE\_PORT} ${BASTION\_USERNAME}@${BASTION\_HOST} -v

# In another terminal

psql "postgresql://${RDS\_DATABASE\_USERNAME}:${RDS\_DATABASE\_PASSWORD}@127.0.0.1:5432/${RDS\_DATABASE\_DB}"

Dump and Restore

pg\_dump 'postgresql://postgres:[email protected]:5432/db' > backup.sql
psql 'postgresql://${RDS\_DATABASE\_USERNAME}:${RDS\_DATABASE\_PASSWORD}@34.29.235.84:5432/restored_db -f backup.sql

Create a User

CREATE USER newuser123 WITH PASSWORD 'foobar123';

# Read only access
GRANT CONNECT ON DATABASE database_name TO newuser123;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO newuser123;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO newuser123;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO newuser123;

# Grant all on database
GRANT ALL PRIVILEGES ON DATABASE database_name TO newuser123;

# Turn User into Super User
ALTER USER newuser123 WITH SUPERUSER;

Useful Stats

-- show running queries (pre 9.2)
SELECT pid, age(clock_timestamp(), query_start), usename, query
FROM pg_stat_activity
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg\_stat\_activity%'
ORDER BY query_start desc;

-- kill running query
SELECT pg_cancel_backend(procpid);

-- kill idle query
SELECT pg_terminate_backend(procpid);

-- vacuum command
VACUUM (VERBOSE, ANALYZE);

-- all database users
select * from pg_stat_activity where current_query not like '<%';

-- all databases and their sizes
select * from pg_user;

-- all tables and their size, with/without indexes
select datname, pg_size_pretty(pg_database_size(datname))
from pg_database
order by pg_database_size(datname) desc;

-- cache hit rates (should not be less than 0.99)
SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit)  as heap_hit, (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
FROM pg_statio_user_tables;

-- table index usage rates (should not be less than 0.99)
SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, n_live_tup rows_in_table
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

-- how many indexes are in cache
SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit)  as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
FROM pg_statio_user_indexes;

Deploying to Cloudflare Pages using Github Actions

Cloudflare provides a great CDN with no egress charges on bandwidth. The best way to use Cloudflare is through Cloudflare Pages. Using Cloudflare Pages should be pretty straightforward for most frameworks that generate a SPA. However, see the example below for how to use Cloudflare Pages from asset pipelines for Ruby on Rails and Django.

Here is an example of using Github Actions to publish Django Static Files

  - name: Build Static Files
   run: |
    docker run --env STATIC_ROOT='/static-compiled/' \
          --env DATABASE_URL='sqlite:///db.sqlite' \
          -v $PWD/static:/app/static -v $PWD/static-compiled:/static-compiled \
          $ECR_REGISTRY/$ECR_REPOSITORY:$IMAGE_TAG \
          python manage.py collectstatic --noinput

  - name: Publish Static Files
   uses: cloudflare/[email protected]
   with:
    apiToken: ${{ secrets.CF_API_TOKEN }}
    accountId: ${{ secrets.CF_ACCOUNT_ID }}
    command: pages publish ./static-compiled --project-name=opszero-static --commit-dirty=true

Setting Github Secrets

Run the following within your repo:

gh secret set nameofsecret "Secret"

This will update the secret to be made available to Github Runners. Alternatively, you can go to the web interface: https://github.com///settings/secrets/actions to update the variable.