Skip to content

Manual Interaction with the Database

Sync the location-string if there's some errors

This code snippet demonstrates how to update the location string for all related locations in a Django application. It is recommended to run this command from the Django shell (or shell_plus) -

$ python manage.py shell_plus

---

from core.models import Location

for location in Location.objects.all()
    location.update_related_stock_locations()

Database Reset Procedure for Development Instance

  1. Login to PostgreSQL: Use the following command to login to PostgreSQL as the 'postgres' user.

    $ psql -U postgres

  2. Delete the Existing Database: Attempt to delete the 'stocks-dev' database with the following command.

    DROP DATABASE IF EXISTS "stocks-dev";

    If you encounter issues due to the database being in use by other services, you can forcefully terminate those connections and rerun the 'DROP DATABASE' command. Use the following SQL query to do so:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'stocks-dev';
  1. Create a New Database and Assign Privileges: Create a new 'stocks-dev' database and assign ownership to the 'embase_ng' user. Then, connect to the new database and grant all privileges on all tables, sequences, and functions in the 'public' schema to 'embase_ng'.
CREATE DATABASE "stocks-dev" OWNER embase_ng;
\c stocks-dev
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO embase_ng;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO embase_ng;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO embase_ng;

Database Backup and Restoration

  1. Create a Database Dump: Use the following command to create a dump of the 'stocks-dev' database and compress it into a gzip file. 

    $ pg_dump stocks-dev | gzip -c - > /tmp/stocks-dev.backup.gz

  2. Restore from a Database Dump: Use the following command to decompress the database dump gzip file and restore it to the 'stocks-dev' database.

    $ gunzip -c /tmp/stocks-dev.backup.gz | psql stocks-dev -U postgres