Skip to content

PostgreSQL books which you should consider

Since the Oracle bought Sun (including MySQL), I wanted to improve my knowledge about other databases and to check the alternatives (in case “something” happens to MySQL).

Luckily, MySQL is still here and it still works fine but in the last couple of months, I wanted to dig a little bit about PostgreSQL. I thought that it would be easy to find online tutorials about this topic but I noticed that online tutorials and forums about PostgreSQL are not so popular. Don’t get me wrong, there are several blogs which fits perfectly in my learning habits but spending so much time just to find the basic things is not what I wanted.

I found a link about Packt Publishing free PostgreSQL backup e-book and I didn’t wait (LINK). I posted a comment and couple days later received Packt account with two books

Instant PostgreSQL Starter by Daniel K. Lyons was exactly what I was looking for. A small (48 pages) book was right away uploaded to my DropBox so I can have it where ever I go. The book covers everything you need to start with Postgresql.
You will learn how to install Postgresql, how to connect and create your first database. The book also covers basic SQL queries for creating tables, CRUD (create, read, update and delete records), etc. The last part of the book contains some features you should also know (how to store passwords, working with XML, Full text search and basic options for improving speed and security). Couple advices about backups are also there.
Using pgAdmin III as a GUI tool won’t be a problem because the book contains pgAdmin screenshots.
Don’t get me wrong…. The book has only 48 pages and it can be compared with a cheat sheet but it is a very good portable book. Learning Postgresql can take only one afternoon and in less than a weekend you’ll became a “usable” DBA.

After the Postgresql starter book, I switched to PostgreSQL Backup and Restore How-to. We usually like to say that backup doesn’t have the price and every DBA should know everything about creating and restoring backups.
This (55 page) book is a step-by-step guide to backing up and restoring your database which covers:

  • basic and partial exports,
  • simple restore,
  • creating and restoring binary backups,
  • compressing backups
  • taking snapshots
  • sync backups
  • point in time recovery
  • warm and hot standby restore
  • streaming replication

Just like the first book, this one is also uploaded to my DropBox. Having those books with you is a nice idea and I highly recommend them to all Postgresql beginners. You can buy them as an e-book and as a hard copy.

Posted in PostgreSQL, Tips & Tricks.

No comments

By Marinko Tarlac – May 21, 2013

CentOS server – nginx howto

Nginx is a free, open-source, high-performance HTTP server and reverse proxy, as well as an IMAP/POP3 proxy server. Nginx now hosts nearly 12.18% (22.2M) of active sites across all domains. Nginx is known for its high performance and low resource consumption.

To add nginx yum repository, create a file named /etc/yum.repos.d/nginx.repo and paste one of the configurations below:

For CentOS

[nginx]
name=nginx repo
baseurl=http://nginx.org/packages/centos/$releasever/$basearch/
gpgcheck=0
enabled=1

For RHEL

[nginx]
name=nginx repo
baseurl=http://nginx.org/packages/rhel/$releasever/$basearch/
gpgcheck=0
enabled=1

Due to differences between how CentOS, RHEL, and Scientific Linux populate the $releasever variable, it is necessary to manually replace $releasever with either “5″ (for 5.x) or “6″ (for 6.x), depending upon your OS version.

Now, be sure that apache is not started

#service httpd stop
#chkconfig --level 235 httpd off

and install nginx with

#yum install nginx

Posted in CentOS, nginx, Server project.

No comments

By Marinko Tarlac – May 13, 2013

Why PostgreSQL is not so popular? (howto part 2)

So… After the first part (Link) where we talk about the installation,
the next step would be to create root user and to change postgres and root password.

[root@XTdata init.d]# su postgres
bash-3.2$ createuser -s root
bash-3.2$ createdb root --owner=root
exit
 
[root@XTdata data]# psql
psql (9.2.4)
Type "help" for help.
 
root=# ALTER USER postgres WITH PASSWORD 'SomePAASWDe348';
ALTER ROLE
root=# ALTER USER root WITH PASSWORD 'SomePAASWDe3489898';
ALTER ROLE
root=# \q

Now, the next step would be to allow remote connections.

postgresql.conf is the main PostgreSQL config file. To be able to reach the server remotely, find the commented line

#listen_addresses = 'localhost'         # what IP address(es) to listen on;

uncomment the line and replace the localhost with the servers IP address. (or replace it with * which means – listen on all interfaces)

listen_addresses = '*'         # what IP address(es) to listen on;

PostgreSQL, by default, refuses all connections it receives from any remote host. The remote hosts can be controled via pg_hba.conf file (located in the same dir like postgresql.conf).

Add the next line

host    all             all             192.168.10.57/32         md5

where 192.168.10.57 is the remote host IP address.

Also, you can allow any host by replacing the 192.168.10.57/32 with 0.0.0.0/0.

The line syntax is

local      DATABASE  USER  METHOD  [OPTIONS]
host       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
hostssl    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
hostnossl  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

which is documented inside the pg_hba.conf. Save the file and restart the server.

I prefer the pgAdmin III tool which can be used for remote management. Fire it up, select File, Add Server… Enter name, host, Username and password.

This should be enough for now…

Posted in CentOS, PostgreSQL, Server project, Tips & Tricks.

No comments

By Marinko Tarlac – May 8, 2013

Logrotate settings

As you probably know, the default logrotate period on RH based distros is 7 days. From my point of view, this number is to big for production servers (files can became extremely large so grep through them can be very slow).

To change this behavior, open /etc/logrotate.conf and replace weekly line with daily. Also, increase the number of files you would like to keep from 4 to something larger (for example 40 or 50 which means 40 or 50 days)

It should looks a like

# see "man logrotate" for details
# rotate log files weekly
#weekly
daily
 
# keep 4 weeks worth of backlogs
rotate 70

Posted in CentOS, Linux, Server project, Tips & Tricks.

No comments

By Marinko Tarlac – April 29, 2013

Extra Packages for Enterprise Linux – EPEL HowTo

EPEL (Extra Packages for Enterprise Linux) is a volunteer-based community effort from the Fedora project to create a repository of high-quality add-on packages that complement the Fedora-based Red Hat Enterprise Linux (RHEL) and its compatible spinoffs, such as CentOS and Scientific Linux.

Adding EPEL repo is very easy:

wget http://ftp.heanet.ie/pub/fedora/epel/6/x86_64/epel-release-6-8.noarch.rpm
rpm -Uvh epel-release-6-8.noarch.rpm

From unknown reason for me, CentOS 6.x goes without php-mcrypt package and it is impossible to install this rpm from base repos. Some apps will complain about this and one of the solutions is to install this rpm from EPEL repo… After you added EPEL repo, type:

yum install php-mcrypt

Posted in CentOS, Linux, MySQL, PHP, Server project.

No comments

By Marinko Tarlac – April 18, 2013

How to disable foreign key checks – MySQL

From time to time you’ll need to delete something from your database and when you try to do it, the MySQL will complain on foreign keys (beside the fact that other table is empty)

This can be solved with disabled foreign keys

SET foreign_key_checks = 0;
DELETE FROM users WHERE id > 45;
SET foreign_key_checks = 1;

You probably noticed that after I deleted those records, I enabled foreign key checks again which you should do also.

Posted in MySQL.

No comments

By Marinko Tarlac – February 19, 2013

Find and delete files

Few months ago, I had a problem after mail server migration. The old mail server decided to die and I had to replace the complete server. I read my logs carefully (on daily basis) and I noticed that hard disk will die so I prepared the complete backup before the damn thing decided to go to the hell.
The old server had the same software like the new one but Dovecot 2.x comes with disabled quota function.
Unfortunately I forget to check this which causes that after 2-3 days I run into problems with more than 15 000 email accounts. Deleted mails was not deducted from used quota and almost all mailboxes was marked as 100% used :( . The solution was to delete all maildirsize files inside mailboxes and let the Postfix to recreate this file as soon as the next email arrives.

As any other problem on Linux, this one also can be solved in one command :) 

find . -type f -name "maildirsize" -exec rm -fv {} \;

Posted in Other, Postfix.

No comments

By Marinko Tarlac – February 19, 2013

Zen coding in Aptana

Zen Coding is a set of plug-ins for text editors that allow for high-speed coding and editing in HTML, XML, XSL, and other structured code formats.

To install ZC plugin,

  • click Help -> Install new software
  • in the “work with” field, type this repo address: http://zen-coding.ru/eclipse/updates/
  • Press “Add…”, give it a name and press OK.

Zen coding should appear on the software list, under “uncategorised”.

You can install it as you’d install any other plugin.

Tested & confirmed to be working with:

Aptana 3.x

Posted in Other, PHP, Programming.

No comments

By Marinko Tarlac – February 19, 2013

Read file line by line with PHP

$fh = fopen("myFile.txt", "r");
while (!feof($fh)) {
	$line = fgets($fh);	
	echo $line;
}
fclose($fh);

Posted in PHP.

No comments

By Marinko Tarlac – November 1, 2012

Nice try a**hole!!!

The thing which scares me is the fact that this phishing email was not recognized by Gmail spam/antivirus/malware/phishing filter…

Posted in AntiVirus.

No comments

By Marinko Tarlac – October 29, 2012