Archive

Archive for the ‘postgresql’ Category

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 -u 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

PostgreSQL Exercises — thoughts…

July 5, 2016 Leave a comment

Welcome to PostgreSQL Exercises! This site was born when I noticed that there’s a load of material out there to help people learn about SQL, but not a great deal to make it easy to learn by doing. PGExercises provides a series of questions and explanations built on a single, simple dataset. It’s designed for […]

via PostgreSQL Exercises — thoughts…

Categories: Interesting, postgresql

Allow remote hosts to connect to PostgreSQL database server on vagrant box

April 26, 2016 Leave a comment

Problem: How to connect to PostgreSQL database server on a vagrant box.
Getting the following error when trying to connect using pgAdmin3.

could not connect to server: 
Connection refused Is the server running on host "192.168.33.10" 
and accepting TCP/IP connections on port 5432? 

Solution:

1. Ensure you have forwarded the ports on the vagrant box.

$less Vagrantfile
  # Create a forwarded port mapping which allows access to a specific port
  # within the machine from a port on the host machine. In the example below,
  # accessing "localhost:8080" will access port 80 on the guest machine.
   config.vm.network "forwarded_port", guest: 5432, host: 5431

  # Create a private network, which allows host-only access to the machine
  # using a specific IP.
   config.vm.network "private_network", ip: "192.168.33.10"

2. Install postgresql database. As per instructions here.

3. Create a user and a database. As per examples here.

4. Change listen_addresses

$ sudo vim /etc/postgresql/9.4/main/postgresql.conf
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)

5. Add the ip of the vagrant box to pg_hba.conf

$ sudo vim /etc/postgresql/9.4/main/pg_hba.conf
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             192.168.33.10/24        md5

6. Restart PostgreSQL database server.

$ sudo service postgresql restart
 * Restarting PostgreSQL 9.4 database server                                      [ OK ]

You should now be able to connect.

Christophe Pettus – PostgreSQL Proficiency for Python People – PyCon 2015

March 13, 2016 Leave a comment

Interesting reads:
http://thebuild.com/blog/ is Christophe Pettus’ software development blog. It has an rss feed.

Categories: postgresql, python