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:
<!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′>
<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’/>
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):
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 220.127.116.11/24 trust[/sourcecode]
This allows the complete 18.104.22.168 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.
First, log on to the testdb database.
[sourcecode]postgres@malbec:/postgres$ psql -d testdb
Type "help" for help.
Then we need a schema in this database:
[sourcecode]testdb=# create schema testschema;
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;
And finally let him do basically anything:
[sourcecode]testdb=# grant all on all tables in schema testschema to testuser;
Finally let’s use the user and create a table and select from it.
[sourcecode]postgres@malbec:/postgres$ psql -d testdb -U testuser
Type "help" for help.
Then we create the table:
[sourcecode]testdb=> create table testschema.testtable( description varchar(25) );
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’);
INSERT 0 1
[sourcecode]testdb=> select * from testschema.testtable;
my first pg record
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