James PK's Technical Journal

[ Home | Journal ]


Sun, 11 Sep 2016

A basic example of installing and setting up Postgresql 9.4 on Debian 8 (Jessie)

A very basic example with a very simple configuration, for further information see Debian Wiki and this informative post by Stuart Ellis

Install the following packages as a minimum (the Debian wiki also recommends installing 3 other packages (see ealier web link))

root@hal:~# apt-get install postgresql postgresql-client

Switch to the postgres user, create an account for an existing user, then create a database called basic_example

root@hal:~# su - postgres
postgres@hal:~$ createuser jamespk
postgres@hal:~$ createdb -O jamespk basic_example

As the user referenced in the above command, connect to the basic_example database and issue some basic SQL to create a table, insert two records, then run a basic select query. SQL sample courtesy of a YO Linux Tutorial

jamespk@hal:~$ psql basic_example
psql (9.4.9)
Type "help" for help.

basic_example=> create table employee (Name char(20),Dept char(20),jobTitle char(20));
CREATE TABLE
basic_example=> INSERT INTO employee VALUES ('Fred Flinstone','Quarry Worker','Rock Digger');
INSERT 0 1
basic_example=> INSERT INTO employee VALUES ('Wilma Flinstone','Finance','Analyst');
INSERT 0 1
basic_example=> select * from employee; name | dept | jobtitle
----------------------+----------------------+----------------------
Fred Flinstone | Quarry Worker | Rock Digger
Wilma Flinstone | Finance | Analyst
(2 rows)

A reminder of what port posgresql runs on and show which config file is being used:

root@hal:~# netstat -pnlt | grep postgres
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 948/postgres
tcp6 0 0 ::1:5432 :::* LISTEN 948/postgres
root@hal:~# su - postgres
postgres@hal:~$ psql
postgres=# show config_file;
------------------------------------------
/etc/postgresql/9.4/main/postgresql.conf
(1 row)

posted at: 00:00 | path: /postgresql | permanent link to this entry


Sat, 09 Jul 2016

Indexing Lightning talk at Postgresql PG Day Conf UK 2016

Very informative day at PG Day UK in London on 5th July with many interesting talks.

A particular lightning talk from a database administrator (DBA) said he had a few queries from colleagues doing support & development, along the lines of "how can I speed up my queries" - a number of times the DBA said after some analysis, he found himself responding...

"...I looked at the tables in question, you should...create index MY_INDEX on MY_TABLE(MY_COLUMN)...ok?". The speaker then said something like - "I've said that so often I feel like having a t-shirt made with that on"

The \d meta command should show existing indexes:

basic_example=# \d employee
       Table "public.employee"
  Column  |     Type      | Modifiers 
----------+---------------+-----------
 name     | character(20) | 
 dept     | character(20) | 
 jobtitle | character(20) | 
Indexes:
    "dept_index" btree (dept)

Obvious caveats for "creating indexes will increase performance" are:

posted at: 00:00 | path: /postgresql | permanent link to this entry


Made with Pyblosxom