PostgreSQL – The Blog of Nitin J Mutkawoa, AKA @TheTunnelix

Tag: PostgreSQL

Messing around PL/Tcl on PostgreSQL

07/04/2016 Nitin J Mutkawoa Add Comment

Some days back, i was having an issue whilst activating the PL/Tcl extension/language on PostgreSQL. Luckily, it was possible with the help from Mauricio Cortes, a guy from the Facebook group PostgreSQL Server. Let’s first see what is TCL package. “PL/Tcl is a loadable procedural language for the PostgreSQL database system that enables the Tcl language to be used to write functions and trigger procedures.” – PostgreSQL This facilitates us to load the module and unload it if same is not being used. You can also activate it for a particular user.

Initially, after installing the package postgresql95-tcl, as per the documentation, i made a “create extension” but same was returning the error “ ERROR: could not open extension control file “/usr/pgsql-9.5/share/extension/pltcl.control : No such file or directory” I also noticed that the file pltcl.control is not present. The guy recommended me to try out a “create language” instead of “create extension” and it works!!.

How silly it may found that, on the official documentation, it was mentioned that the create language has been deprecated and now the create extension is being used. I immediately understand that i was using a bad repository. I would advice to use the official rpm file from the official website yum.postgresql.org.

Lets see how to activate it:

1.Simply fire a yum install postgresql95-tcl Make sure that you are using the right repo (https://www.postgresql.org/download/linux/redhat/) to be able to use the create extension

2.After login, you can verify same with the command \dx to have a list of installed extensions or \dx+ to have more details about the object

3. Now, here is the catch, if you are using PostgreSQL having the version older than 9.5, this should be activated with a create language pltcl; otherwise if its version 9.5 or beta 9.6, you can use a create extension pltcl;.

After some more research i notice that the advantage of this feature is that when a create extension is performed for any plugin the dump of the database will also consist of the extension otherwise in older version the Tcl language would not be included in the dump.

Articles published on PostgreSQL:

Database Systems, Linux System linux, PostgreSQL

Backup, Restore and work on PostgreSQL cmd line

03/31/2016 Nitin J Mutkawoa Add Comment

Last time, we have a look how to install and set up PostgreSQL on a CentOS machine as well as to create a database. Let’s now see some more details about backups and database restore as well as some commands to master the basic of psql on command line.

1.We have created a database called test. Here is an example how you create a table on Postgres

test=# create table hackers( id int primary key not null, country char(20), number int not null, project varchar(50) );

2. You can view your table with the command \d or \d <table name>

3. To perform a dump of a database log in as the user which have access to the database and launch the following command.

pg_dump test > db_backup.sql

To restore the same backup on a postgres machine, you need to have the database created first

psql test < db_backup.sql

4. A little work around of psql around the database for example accessing directly the database test with the command psql test and to list all the database can be done with \l

5. Let’s say you want to switch between database test and toto. This can be done with \c test and \c toto

6. To show all tables inside the database can be done with the command \dt

7. The schema of the table can also be seen with the command \d+ <table name> Here, it will be \d+ hackers

8. All users and permissions can be viewed with \du

9. Databases are create and drop inside the psql command line ending with a semi column

10. You can also alter user role with superuser and non superuser

11. You can also output the result of a select statement from the psql prompt directly using the following (For example the to output the result of a select statement to ~/SelectStatement.out) :

postgres=# \o ~/SelectStatement.out - Start logging the output
postgres=# Select * from users;     - The command
postgres=# \o                       - Stop the logging

Database Systems centos, PostgreSQL

Install, Setup and Create DB on PostgreSQL

03/30/2016 Nitin J Mutkawoa Add Comment

PostgreSQL is yet another open source object relational database system. Its compatible with almost all operating system including BSD, Windows and Linux. I will be using a Centos7 machine to install a PostgreSQL and set up some basics of PostgreSQL. In a next article, i will give some idea of something more robust you can do with PostgreSQL.

1.You can install PostgreSQL from the repository with the following command

yum install postgresql-server postgresql-contrib

2. Now the first action you need to perform after PostgreSQL server installation is to initialize the database by creating a new database cluster

postgresql-setup initdb

3. You can now start the postgresql service. Postgres will be listening on port 5432

systemctl start postgresql

4. Now that we have PostgreSQL installed on our machine, we can create the first super user with following command. Let’s called the user test. Its a practice to use the username having sudo privilege on the machine itself.

sudo -u postgres createuser --superuser  test

5. To connect on the Postgres command line use the following. postgres is the default user and psql is what u what to run

sudo -u postgres psql

6. You should have something similar to this.

7. Now you can set the password for the superuser that you have just created. In my case it is user ‘test’ at step 4

\password test

8. Once you have been prompted to enter the password twice means that you have already set up PostgreSQL. You can exit with the command

\q

9. To connect to the default postgres database you simply need to use the command. To quit follow step6

psql postgres

10. Lets create a database with the superuser. You might need to add your user to the group postgres with the command usermod -g test postgres as postgres will need permission to access your home directory to drop the .psql_history file

sudo -u postgres createdb test

11. To get on the command line you just need to type psql which should show you something similar to this

Database Systems, Linux Application centos, PostgreSQL