Archive

Archive for the ‘postgresql’ Category

Getting Started with Heroku – notes

January 6, 2020 Leave a comment

Notes:

1. Install the heroku cli.
2. If you have a preexisting git repository with the desired files to be pushed to heroku.
You need to have at minimum a Procfile and requirements.txt file

$ less Procfile
web: gunicorn wsgi:app
$ less requirements.txt
Flask==1.0.3
alembic==1.0.10
...
gunicorn==20.0.4

3. Merge changes into master branch if working off a different branch.
4. Login into heroku.
$ heroku login
5. Create a heroku app. You can specify a name for the app. Below command generate a random name.
$ heroku create
To create an app outside the default region of the us.
$ heroku create midnight-cask-76349 --region eu
6. Create a heroku postgres database.
$ heroku addons:create heroku-postgresql:hobby-dev
7. Push changes up to heroku app. Alembic changes get applied automatically on successful deploy.
$ git push heroku master
8. Set application `Config vars` under settings on the dashboard
9. Get database application details from dashboard. Follow link from database details under installed addons section. THe database url details will be available under the linked settings. Also have instructions on how to connect to database
$ heroku pg:psql postgresql-sparkle-97339 --app midnight-cask-76349
10. To manually run alembic
$ heroku run alembic upgrade head
11. To check logs
$ heroku logs --tail
12. To get database credentials
$ heroku pg:credentials:url
13. You can clone the heroku repo for the app locally
$ heroku git:clone -a midnight-cask-76349

Sources:
https://stackabuse.com/deploying-a-flask-application-to-heroku/
https://stackoverflow.com/questions/34379130/pushing-to-heroku-fails-no-such-app-fatal
https://elements.heroku.com/addons/heroku-postgresql
https://www.digitalocean.com/community/tutorials/how-to-serve-flask-applications-with-gunicorn-and-nginx-on-ubuntu-14-04
https://blog.dbi-services.com/a-free-postgresql-cloud-database/
https://devcenter.heroku.com/articles/heroku-postgresql#designating-a-primary-database

Categories: postgresql, python Tags:

Postgres “Select * from ” freezes

August 18, 2019 Leave a comment

Problem:
Due to a programming error in a python program I had written, I was unknowingly preventing postgres from obtaining an access lock to a table. There were no errors but trying to run a

Select * from <table>

would just freeze.

Solution:
The solution was to fix the python program. But I used the following query to get some more insight into what was causing the issue.

$ psql -U myuser -h localhost mydatabase
Password for user myuser: 
psql (10.8 (Ubuntu 10.8-1.pgdg14.04+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

mydatabase=> SELECT *                                  
  FROM pg_locks l
  JOIN pg_class t ON l.relation = t.oid AND t.relkind = 'r'
 WHERE t.relname = '<table>';
 pid  
------
 2028
 2159
 2029
 2357
 2106
 2409
 2128
 2387
 2084
(9 rows)

mydatabase=> \q


Source:

https://stackoverflow.com/questions/10317114/postgresql-drop-table-doesnt-work

Categories: postgresql

Beginner’s Guide to Using Databases with Python: Postgres, SQLAlchemy, and Alembic

March 27, 2019 Leave a comment

Running Flask on Kubernetes

October 1, 2018 Leave a comment

Posted by Michael Herman on Sep 19, 2018

In this post, we’ll first take a look at Kubernetes and container orchestration in general and then we’ll walk through a step-by-step tutorial that details how to deploy a Flask-based microservice (along with Postgres and Vue.js) to a Kubernetes cluster.

https://testdriven.io/running-flask-on-kubernetes

Source:
https://pycoders.com

Categories: flask, postgresql, python Tags: , ,

Generating JSON from SQL

May 3, 2017 Leave a comment


Generating JSON in PostgreSQL can be several times faster than copying the relational data to the application and then generating the JSON.

Source:
https://www.pgcasts.com/episodes/1/generating-json-from-sql/

Categories: Interesting, postgresql Tags: , ,

PostgreSQL – Create users and assign roles

January 25, 2017 Leave a comment

1. Create role.

CREATE ROLE myuser LOGIN PASSWORD 'mypassword';

2. Alter role.

ALTER ROLE myuser CREATEDB CREATEROLE;

3. Create database.

$ createdb mydatabase -U myuser -h 127.0.0.1 -p 5432;

4. Connect as new user.

psql -h 127.0.0.1 -p 5432 -U myuser;

5. Delete role

DROP OWNED BY myuser cascade;
DROP ROLE myuser;
$ sudo su postgres
$ psql postgres
psql (9.4.10)
Type "help" for help.

postgres=# CREATE ROLE myuser LOGIN PASSWORD 'mypassword';
CREATE ROLE
postgres=# \dg
                                List of roles
   Role name    |                   Attributes                   | Member of
----------------+------------------------------------------------+-----------
 myuser         |                                                | {}
 postgres       | Superuser, Create role, Create DB, Replication | {}
 
 vagrant        |                                                | {}

postgres=# ALTER ROLE myuser CREATEDB CREATEROLE;
ALTER ROLE
postgres=# \dg
                                List of roles
   Role name    |                   Attributes                   | Member of
----------------+------------------------------------------------+-----------
 myuser         | Create role, Create DB                         | {}
 postgres       | Superuser, Create role, Create DB, Replication | {}
 
 vagrant        |                                                | {}

postgres=#\q



$ psql -h 127.0.0.1 -p 5432 -U myuser;
Password for user myuser:
psql: FATAL:  database "myuser" does not exist



$ createdb mydatabase -U myuser -h 127.0.0.1 -p 5432; 
Password:



$ psql -h 127.0.0.1 -p 5432 -U myuser -d mydatabase; 
Password for user myuser:
psql (9.4.10)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

mydatabase=> \dt
No relations found.

mydatabase=> \q

Source:
https://www.postgresql.org/docs/current/static/sql-createrole.html
http://grokbase.com/t/postgresql/pgsql-patches/068m331a82/drop-owned-by-doesnt-work

Categories: notes, postgresql Tags: ,

Working with Dates and Times in PostgreSQL

December 6, 2016 Leave a comment
Categories: Interesting, postgresql