Archive

Archive for the ‘postgresql’ Category

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

Advertisements
Categories: postgresql

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

March 27, 2019 Leave a comment

https://www.learndatasci.com/tutorials/using-databases-python-postgres-sqlalchemy-and-alembic/

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

Querying json in PostgreSQL

September 7, 2016 Leave a comment
Categories: postgresql