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.


First I created an osuser / group

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

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

[sourcecode]bunzip2 < postgresql-9.6.3-S11.i386-64.tar.bz2 | tar xpf -[/sourcecode]

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


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:

export PG_HOME PATH LD_LIBRARY_PATH[/sourcecode]

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:

[sourcecode]$ initdb -D /opt/postgres/data[/sourcecode]

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

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

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

su – postgres -c "/opt/postgres/9.6-pgdg/bin/64/pg_ctl start -l logfile -D /opt/postgres/data"[/sourcecode]

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:

[sourcecode]<?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’/>
<dependency name=’network’ grouping=’require_all’ restart_on=’none’ type=’service’>
<service_fmri value=’svc:/milestone/network:default’ />
<dependency name=’filesystem’ grouping=’require_all’ restart_on=’none’ type=’service’>
<service_fmri value=’svc:/system/filesystem/local:default’ />
<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’/>
<loctext xml:lang=’C’>pg</loctext>

That can be easily imported with

[sourcecode]svccfg import /root/pgstart.xml[/sourcecode]

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

[sourcecode]createdb testdb[/sourcecode]

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:

[sourcecode]host all all trust[/sourcecode]

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


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.

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

Type "help" for help.

testdb=# [/sourcecode]

Then we need a schema in this database:

[sourcecode]testdb=# create schema testschema;
testdb=# [/sourcecode]

And of course we need a user

[sourcecode]testdb=# create user testuser password ‘testpwd’;

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.

[sourcecode]testdb=# grant all on schema testschema to testuser;
testdb=# [/sourcecode]

And finally let him do basically anything:

[sourcecode]testdb=# grant all on all tables in schema testschema to testuser;
testdb=# \q

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

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


Then we create the table:

[sourcecode]testdb=> create table testschema.testtable( description varchar(25) );
testdb=> [/sourcecode]

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:

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

Let’s use it:

[sourcecode]testdb=> insert into testschema.testtable(description) values (‘my first pg record’);
testdb=> [/sourcecode]

[sourcecode]testdb=> select * from testschema.testtable;
my first pg record
(1 row)

testdb=> [/sourcecode]

[sourcecode]testdb=> commit;
WARNING: there is no transaction in progress

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

Leave a Reply

Your email address will not be published. Required fields are marked *

1 + thirteen =

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: