# PostgreSQL and Alembic Setup I'm noting a bunch of commands so I don't have to keep Googling. It should be helpful if you're restarting or starting fresh. ## If Restarting, Delete Alembic Both the `alembic` folder at root and `alembic.ini` at root. ## Connect to DB# ```bash psql --echo-all --host=localhost --port=5432 --dbname=newshack_db --username=postgres --no-password ``` or ```bash psql -h localhost -U postgres -d newshack_db ``` or ```bash sudo -i -u postgres ``` to login and then `psql` to get to terminal. If you're having trouble connecting to DB Manager, or if it's requesting a password when you don't have one, find your psql version, navigate to your config file, and change all the login options to trust from md5 or whatever it is. ## Terminate Active Connections ```sql SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'newshack_db' AND pid <> pg_backend_pid(); ``` or ```bash psql --host=localhost --port=5432 --username=postgres --no-password -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'newshack_db' AND pid <> pg_backend_pid();" ``` ## If Restarting, Delete DB ```bash psql --host=localhost --port=5432 --username=postgres --no-password -c "DROP DATABASE IF EXISTS newshack_db;" ``` or ```sql DROP DATABASE IF EXISTS newshack_db; ``` # If Restarting from Absolute Scratch (for Fresh Postgres install): - `sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh` - `sudo apt install postgresql-client` - `sudo apt install postgresql` - `sudo systemctl start postgresql` - `sudo -u postgres psql -c "CREATE DATABASE newshack_db;"` (create with peer connection) - sudo -u postgres psql (connect with peer authentication) - `CREATE DATABASE newshack_db;` - `\q` - sudo -u postgres psql -d newshack_db - create password with `sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD '3646';"` - update alembic env.py and postgres session.py with `postgresql://postgres:3646@localhost:5432/newshack_db` (==which includes the created password==) - starting the FastAPI should create the tables ([[Starting Svelte Dev Server]]) - Instructions for backup restoration below ## Create DB ``` sudo -u postgres psql -c "CREATE DATABASE newshack_db;" ``` or ```bash psql --host=localhost --port=5432 --username=postgres --no-password -c "CREATE DATABASE newshack_db;" ``` or ```sql CREATE DATABASE newshack_db; ``` ## Configure Alembic ```bash alembic init alembic ``` ## Update alembic.ini with Path and sqlalchemy.url ex (if not at root): ``` [alembic] # path to migration scripts script_location = backend/alembic ``` and: ``` # root/alembic.ini sqlalchemy.url = postgresql://postgres@localhost:5432/newshack_db ``` ## Update alembic/env.py with DB Models ex: ``` python # alembic/env.py # Import your base metadata and models from backend.domain.base import Base # Specifying all ORM models here still may be necessary even after importing Base... from backend.domain.client.client_orm import Client from backend.domain.doc.doc_orm import Doc from backend.domain.cluster.cluster_orm import Cluster from backend.domain.cluster.cluster_association import ClusterAssociation # Set target_metadata to the Base.metadata target_metadata = Base.metadata target_metadata = Base.metadata ``` However, importing the specific models is required inside `init_db()` before/where the PostgreSQL Session is instantiated. ## Generate Initial Migration ```bash alembic revision --autogenerate -m "initial migration" ``` E.g., "rename tables and fields for doc_topic → cluster and add fields to client table (summary, webhook_url)" or, if not at root: ``` alembic -c backend/alembic.ini revision --autogenerate -m "initial" ``` ## Make Initial Migration ```bash alembic upgrade head ``` or if not at root: ``` alembic -c backend/alembic.ini upgrade head ``` If a migration is giving you trouble, you can edit it and try again or delete it. If you're stalling, Terminate Active Connections. ## Confirm Schema To confirm if changes were made properly, and because DB Browser plugin can lag, run the following: 1. **Login to DB:** ``` sudo -u postgres psql ``` ```bash psql --host=localhost --port=5432 --username=postgres --dbname=newshack_db ``` 2. **List all tables:** ```sql \dt ``` 3. **Describe a table:** ```sql \d docs ``` ## [[Pycharm's DB Browser not Displaying Columns]] ## Add Doc Data from CSV: 1. Add the client to the client table if it does not already exist. 2. Connect to the DB: ```bash psql --echo-all --host=localhost --port=5432 --dbname=newshack_db --username=postgres --no-password ``` 3. Copy the CSV:d ```sql \copy docs(doc_id, client_id, title, text, date, url) FROM '/home/ethan/Downloads/NewsHack PostgreSQL Tables - docs.csv' DELIMITER ',' CSV HEADER; ``` ``` \copy docs(doc_id, client_id, title, text, date, url) FROM '/home/ethan/PycharmProjects/newsHack/backend/postgres/exported_tables/docs.csv' DELIMITER ',' CSV HEADER; \copy doc_topics(topic_id, topic_name, created_at) FROM '/home/ethan/PycharmProjects/newsHack/backend/postgres/exported_tables/doc_topics.csv' DELIMITER ',' CSV HEADER; \copy doc_topic_associations(association_id, doc_id, topic_id) FROM '/home/ethan/PycharmProjects/newsHack/backend/postgres/exported_tables/doc_topic_associations.csv' DELIMITER ',' CSV HEADER; \copy clients(client_id, client_name, contact_email, created_at) FROM '/home/ethan/PycharmProjects/newsHack/backend/postgres/exported_tables/clients.csv' DELIMITER ',' CSV HEADER; ``` --- Restart faster by deleting anything inside `alembic/versions` and dropping any rows inside the `alembic_version` table from your database, as it tracks the current migration state. But after generating the migration script, you may need to `nano backend/alembic/versions/<#>_initial.py` and add this line before upgrading: ``` def upgrade(): # Drop tables with cascading to handle foreign key constraints op.execute('DROP TABLE doc_topic_associations CASCADE') op.execute('DROP TABLE clients CASCADE') op.execute('DROP TABLE doc_topics CASCADE') op.execute('DROP TABLE docs CASCADE') ``` If it seems an alembic migration script is persistent on removing tables for no reason, it may be failing to register your orm models (env.py). # Making Migrations Stalling? Terminate active connections: ``` psql --host=localhost --port=5432 --username=postgres --no-password -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'newshack_db' AND pid <> pg_backend_pid();" ```