Tasting a different flavour; postgres done by an oracle dba

Tasting a different flavour; postgres done by an oracle dba

There are moments in life you put everything on a row and you want to taste a slight different flavour. Well, I had a week off and I had a play with the plain vanilla postgres. Basically I should try the EDB as it is a bit more “oracle-like”, but I had a spare solaris zone and didn’t feel like running linux, so … plain vanilla it is.

First steps? Easy as … I would say anything. In my homelab I run a opensolaris version and the pkgsrc build even contains all you need. If you want to give it a go, you can check it here ( http://pkgsrc.joyent.com ) and specifically for illumos / smartos you find it here ( http://pkgsrc.joyent.com/install-on-illumos/ ).

Because that would be TOO easy (and also because this zone is not internet/network connected) I decided to do it manually. The code can be easily downloaded from this site: https://www.postgresql.org/ftp/binary/v9.6.3/solaris/solaris11/i386/  Anyhow, installation was very easy, let me run through.

Installation

First I created an osuser / group

groupadd -g 1000 postgres
useradd -u 1000 -g postgres -d /postgres -m -s /usr/bin/ksh postgres

Then I transferred the tar.bz2 file to /opt/postgress. Installation itself is:

bunzip2 < postgresql-9.6.3-S11.i386-64.tar.bz2 | tar xpf -

So … that’s it folks … You installed postgres on solaris.

Configuration

That is also very easy. What do we want? A database! When do we want it? As fast as possible. Ok that can be done like this. First i added this in my .profile to make my life a bit easier:

PG_HOME=/opt/postgres/9.6-pgdg
PATH=$PG_HOME/bin/64:$PATH
LD_LIBRARY_PATH=/opt/postgres/9.6\-pgdg/lib/64:/opt/csw/lib/amd64
export PG_HOME PATH LD_LIBRARY_PATH

So I can access the psql like I’m used to sqlplus. Then I wanted to create a database, but remember, you need to start the service first. Before you can start the service, the data directory needs to be inited first:

$ initdb -D /opt/postgres/data

this inits the directory and gives us the possibility to start the service:

/opt/postgres/9.6-pgdg/bin/64/pg_ctl start -l logfile -D /opt/postgres/data

I also wrote a little script I could use in my smf to start it automatically as the postgres user. Not too difficult:

#!/usr/bin/bash
su - postgres -c "/opt/postgres/9.6-pgdg/bin/64/pg_ctl start -l logfile -D /opt/postgres/data"

you see, nothing fancy in that. This is maybe a bit too particular for my case, but I include it for my own reference. This is the xml I used:

<?xml version='1.0'?>
<!DOCTYPE service_bundle SYSTEM '/usr/share/lib/xml/dtd/service_bundle.dtd.1'>
<service_bundle type='manifest' name='export'>
<service name='postgres/pgserver' type='service' version='0'>
<create_default_instance enabled='true'/>
<single_instance/>
<dependency name='network' grouping='require_all' restart_on='none' type='service'>
<service_fmri value='svc:/milestone/network:default' />
</dependency>
<dependency name='filesystem' grouping='require_all' restart_on='none' type='service'>
<service_fmri value='svc:/system/filesystem/local:default' />
</dependency>
<exec_method name='start' type='method' exec='/root/startpg.sh' timeout_seconds='60'/> <!-- script -->
<exec_method name='stop' type='method' exec=':kill' timeout_seconds='60'/>
<stability value='Unstable'/>
<template>
<common_name>
<loctext xml:lang='C'>pg</loctext>
</common_name>
</template>
</service>
</service_bundle>

That can be easily imported with

svccfg import /root/pgstart.xml

Good, we’re all set now and we create the database. Just as the postgres user (on the os cli):

createdb testdb

And that’s basically it. Postgress is secure on it’s own, so you need to open it a bit to allow remote connections. To do so edit the /opt/postgres/data/pg_hba.conf configuration file and add a network or a host (i give the anonymised example of a network).  That is simply done by adding this line:

host all all 123.123.123.0/24 trust

This allows the complete 123.123.123.0 network to access the database and also edit the /opt/postgres/data/postgresql.conf file to add following line:

listen_addresses='*'

by default this parameter is set to localhost. I like to compare it to the invited nodes in the sqlnet.ora.

Using it

First, log on to the testdb database.

postgres@malbec:/postgres$ psql -d testdb
psql (9.6.3)

Type "help" for help.

testdb=# 

Then we need a schema in this database:

testdb=# create schema testschema;
CREATE SCHEMA
testdb=# 

And of course we need a user

testdb=# create user testuser password 'testpwd';
CREATE ROLE
testdb=#

Now we have a user, but he can’t do much, so for playtime, we give him all rights. For production, you must think about the principle of least privilege of course.

testdb=# grant all on schema testschema to testuser;
GRANT
testdb=# 

And finally let him do basically anything:

testdb=# grant all on all tables in schema testschema to testuser;
GRANT
testdb=# \q
postgres@malbec:/postgres$

Finally let’s use the user and create a table and select from it.

postgres@malbec:/postgres$ psql -d testdb -U testuser
psql (9.6.3)
Type "help" for help.

testdb=>

Then we create the table:

testdb=> create table testschema.testtable( description varchar(25) );
CREATE TABLE
testdb=> 

Notice that I have to use the schema and not the user. If you try to use the user, it starts throwing errors to you:

testdb=> create table testuser.testtable2( description varchar(25) );
ERROR: schema "testuser" does not exist
LINE 1: create table testuser.testtable2( description varchar(25) );
^
testdb=>

Let’s use it:

testdb=> insert into testschema.testtable(description) values ('my first pg record');
INSERT 0 1
testdb=> 
testdb=> select * from testschema.testtable;
description
--------------------
my first pg record
(1 row)

testdb=> 
testdb=> commit;
WARNING: there is no transaction in progress
COMMIT
testdb=>

apparently autocommit is on, so … that might be the next step. To check out those little things.

One thing I found useful. I usually create my playgarden schema’s using sql modeler included in sqldeveloper and let the script generated for me. Then I found this website: http://www.sqlines.com/online  There you can copy paste your scripts and let them translate between database dialects. For a lab, pretty useful I think if you want to create some schema’s but don’t want to translate all things yourself. So that’s it for now.

As always, questions, remarks? find me on twitter @vanpupi

One thought on “Tasting a different flavour; postgres done by an oracle dba

Comments are closed.

Comments are closed.
%d bloggers like this: