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
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.
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):
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 220.127.116.11/24 trust
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.
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