Category Archives: System Administration

sysadmin

How to connect to postgresql after a fresh Install

I had a sad experience after installing postgresql and pgAdminIII, because I could not connect to the database. On pgAdminIII interface, I used to put the root password but it would not work. Actually, after Installing postgresql, you need to set a password. To do this, follow the 3 steps below:

  1. Connect to Postgres as User Postgres
  2. Set password
  3. Exit
  1. Connect to database Postgres as User Postgres

Type the following in the terminal:

 sudo -u postgres psql postgres 
  1. Set password

to update your password type the command

\password
  1. Exit

If the password is successfully set, then Exit using the command:  \q

Now you can use pgAdminIII to connect to your postgres server. In the password field enter the password you created in step 2.

Sources / Further Reading

Advertisements

why choose Postgresql

For some time now I have been using Postgresql and I think this is the right moment to outline 3 reasons why I think it is “The world’s most advanced open source database” just as the people behind it claim.

These reasons are:

  1. Data Integrity
  2. Extensions
  3. Replication
  1. Data Integrity

PostgreSQL strives to respect ANSI-SQL:2008  ACID principle (Atomicity, Consistency, Isolation and Durability). Features such as Constraints, Triggers, Commit/Rollback and Write Ahead Logging (WAL) ensures that only holistic and validated data get stored.

Features:

  • Constraints : Primary keys, foreign keys, NOT NULL, Unique constraints … ensure that data conforms to specifications before it gets stored.
  • Triggers : These are user written procedures that fire during specified CRUD operations. They are use to guarantee Consistency.
  • Commit/Rollback: PostgreSQL allows for the grouping of transactions into logical units such that if any part of the unit fails ,then the entire unit itself fails. This is the all or Nothing principle where each unit is considered as indivisible. An example could be the transfer of money from account A to account B, if the deposit (INSERT)  into account B fails , then the withdrawal(UPDATE) from account A should roll back as if it never happened. Otherwise, the whole transaction should be committed. Commit/Rollback enforces Atomicity
  • Write Ahead Logging (WAL): The idea behind WAL is that all modifications on a database  are written to a log before they are applied. This allows for recovery during a crash.  To illustrate this concept consider the following example:  A machine suddenly crashes when a program is in the middle of performing some database operation. Upon restart, the program might need to know whether the operation succeeded, half succeeded or failed. If the Write Ahead Log is used the program might check this log to compare with what it was supposed to do when the system crashed. On the basis of this comparison, the program might decide to complete the transaction or simply abort it. Hence, WAL allows for Atomicity and Durability in database systems

 

  1. Extensions

It’s more powerful, more reliable, and has a better set of features than any other open source data storage layer out there today. It’s extensible via projects like PL/V8 (worthy of entire topic all to itself) and it has tools built into it like full text search that require you to take on whole other *services* when you use other databases.

These are the words of one of the old timers at Heroku : a cloud platform that supports many programming languages.  The fragment of the phrase that is of interest here is: it’s extensible. 

Wow postgreSQL is really extensible. These extensions are:

  • PostGIS: This extension adds support for Geographic Information Systems (GIS) object.  GIS objects include land, trees, roads, houses, restaurants …. etc.  PostGIS adds new datatypes POINT, LINE, POLYGON, … which enables to collect information from these objects.  With PostGIS, you can answer questions such as “Find the closest restaurant near me
  • PostPic: This extension allows for image processing. It adds functions such as cropping, resizing and rotation. It equally enables to extract image attributes (height, weight, date …). For more on this extension visit http://github.com/drotiro/postpic

A variety of other extensions can be found here

  1. Replication

Replication simply means maintaining an exact copy of the database in production (primary database) so that in an event of its crash this copy (secondary database)  can be used. It is a feature that promotes high availability.  Before Postgres 8.4, this was not possible and it was the principal reason behind people going in favor of  Mysql that had far advanced replication capabilities. As of version 8.4 Postgres now supports many replication techniques including hot standby, warm standby , slony replication ….

 

To wrap up, I guess you now have sufficient arguments to be convinced about the powerful nature of postgreSQL. Its open source, its reliable, its robust. Its your tool, use it to your taste.

BONUS: You may be dwelling on whether to choose MySQL or Postgres. If your concern is  rigorous data integrity checks (e.g you deal with financial info ) then Postgres probably is a better choice. If rather your concern is SPEED and simplicity over strict data integrity than MySQL is better.

Sources / Further Reading

 

 

How to backup automatically your postgresql database everyday at 15h30mins

You can produce backups of your  database automatically using both pg_dump and crontab. pg_dump is used to produce the backup while crontab performs the automation.

In this tutorial, I perform a backup everyday at 15h30 mins.

The first first two steps below are necessary to built the routine while there is an optional 3rd step added for the sake of clarity.

  1. create the backup with pg_dump command
  2. Put the command inside a cron job 
  3. Optional: Renaming the backups
    1. create the backup with pg_dump command

The pg_dump allows for the creation of backup.  It can take several arguments.

In general, the format of the pg_dump command is as follows:

 /usr/bin/pg_dump --host [HOST_IP] --port 5432 --username "[DB_USER]" --format custom --blobs --no-owner --schema=public --verbose --file "[DESTINATION_FOLDER]" "[DB_NAME]"
  • HOST_IP can be localhost or any other IP address where Postgresql is found
  • DB_USER represents the user who has been granted permissions over the database DB_NAME
  • DESTINATION_FOLDER: path to which we want to store the backup

To illustrate the above concepts, let us take this specific example

Example: consider the user sales_user that owns the database sales. You want to locally produce a backup of your database and store it  inside the directory /home/user/Documents/databases, then use the  command:

 /usr/bin/pg_dump --host localhost --port 5432 --username "sales_user" --format custom --blobs --no-owner --schema=public --verbose --file "/home/user/Documents/databases/sales_14_04_2016-15h30.backup" "sales"

For more information on the arguments used view postgresql docs

  1. Put the command inside the cron job

To create a cron that executes every day at 15h30 do the following:

  • open your terminal and edit the cron job file using :
 crontab -e 
  • add the record at the bottom of the file
30 15 * * * /usr/bin/pg_dump --host localhost --port 5432 --username "sales_user" --format custom --blobs --no-owner --schema=public --verbose --file "/home/user/Documents/databases/sales_14_04_2016-15h30.backup" "sales"
  • save and close it

This command executes every day at 15h30

NOTE: In the crontab file records are of the form :

MINUTE HOUR  DAY_OF_MONTH MONTH DAY_OF_WEEK  COMMAND.

Everything after the hash mark (#)is considered as a comment.

 

  1. Optional: Renaming the backups

After having set up the cron, you would notice that everyday at 15h30mins the system overwrites the backup produce the previous day since both carry the same name. This is somewhat embarrasing as your would want to have history of your backups.

Moreover, it is even better that each backup file has a significant name i.e the date and time when it was produce e.g sales_20-10-2016-15h30min.backup representing the backup produced on the 20th october 2016 at 15h30 mins.

Luckily, this is can be done when the cron is created. By using the date command to format the output file we can arrive at this. The command is thus:

30 15 * * * /usr/bin/pg_dump --host localhost --port 5432 --username "sales_user" --format custom --blobs --no-owner --schema=public --verbose --file "/home/user/Documents/databases/$(date '+sales-\%d-\%m-\%Y-\%HH-\%Mmin').backup" "sales"

NOTE: The interesting part in this command is:

 $(date '+sales-\%d-\%m-\%Y-\%HH-\%Mmin') 

Here we use command substitution i.e when the shell encounters this part of the command it substitutes the command with its output. To enable command substitution use $(…)

  • Further notes on command substitution can be found here
  • Further notes on formatting with the date command can be found here or by executing man date in the shell.

I hope this tutorial helps. Thank you!!!

Sources/Further reading:

How to enable remote access to PostgreSQL server database?

You can enable remote access to a postgreSQL database using these 4 steps:

  1. Add Client Authentication Record in pg_hba.conf
  2. Change Listen Address in postgresql.conf
  3. Restart Server
  4. Test Set-Up

 

  1. Add Client Authentication Record in pg_hba.conf

pg_hba.conf controls client authentication. HBA stands for Host Based Authentication. The file consist of a set of records, one per line. Text after # are considered as comments. Each record has a number of fields which are separated by spaces and/or tabs.

Each record specifies a connection type ( typically host or local ), a client IP address range (if relevant for the connection type), a database name, a user name, and the authentication method to be used for connections matching these parameters.

The first uncommented record is used to perform authentication. For more information you can cat pg_hba.conf or visit postgresql docs

Procedure:

    • open pg_hba.conf with the right permissions (generally as a user find in the sudoers file). pg_hba.conf is located in /etc/postgresql/9.3/main in ubuntu systems
    • add the record
 host all all 0.0.0.0/0 trust 

This line enables tcp/ip connections (host) for all databases (all) by all users (all) from every client ip address range (0.0.0.0/0). Obviously, You can tighten security for client authentication  using different options. These options can be found in postgresql docs or by performing a cat of pg_hba.conf

 

  1. Change Listen Address in Postgresql.conf

Postgresql parameters are set using the postgresql.conf file. To view current parameters use the command

show  all;

Just like pg_hba.conf, records are one per line and hash marks (#) designate the remainder of the line as comments

Procedure:

    • open postgresql.conf with the right permissions (generally as a user find in the sudoers file). postgresql.conf is located in /etc/postgresql/9.3/main in ubuntu systems
    • find and Edit the line “listen_address” to
listen_address = '*'
  • you can specify a particular IP address to listen on. * in this case mean the server listens for request from all IP addresses. Do not forget to uncomment the line, if it is commented
  • Save and close the file
  1. Restart Server

After editing the files pg_hba.conf and postgresql.conf, we need to ensure postgresql reads the new configuration. One way to that is by restarting the server:

sudo service postgresql restart 
  1. Test Set-Up

The most important step is to ensure that all what was done above, finally worked. A simple way of doing that is to connect to postgres via a client workstation.

As an illustration, if your database server ip is 192.168.1.104 and there exists a database sales owned by the user postgres, then you may use the following command to test your setup

 psql -h 192.168.1.104 -U postgres -d sales 

 

Sources/Further Reading:

Who is a System Administrator?

A system administrator or sysadmin is a person that ensures that all related computer systems and services keep working.

The term system administrator comes into play when we are in a multi-user enviroment characterised by the use of servers.

They are responsible of the efficiency and performance of the system

The duties of a system administrator are wide-ranging, and vary from one organization to another. In general their duties include:

  1. User administration (setup and maintaining account)
  2. Installing system-wide software
  3. setting up new hardware and software.
  4. Create a backup and recover policy
  5. Script writing to provide automation routine …

 

References

https://en.wikipedia.org/wiki/System_administrator

http://www.cyberciti.biz/faq/what-is-the-role-of-the-system-administrator/

http://www.dictionary.com/browse/system-administration

http://www.dictionary.com/browse/system-administration