≡ Menu


MySQL vs PostgreSQL

WikiVS has published detailed comparison between MySQL and PostgreSQL. From the pages:

MySQL vs PostgreSQL is a decision many must make when approaching open-source relational databases management systems. Both are time-proven solutions that compete strongly with propriety database software. MySQL has long been assumed to be the faster but featureless of the two database systems, while PostgreSQL was assumed to be a more densely featured database system often described as an open-source version of Oracle. MySQL has been popular among various software projects because of its speed and ease of use, while PostgreSQL has had a close following from developers who come from an Oracle or SQL Server background.

=> MySQL vs PostgreSQL from the open comparison website.

Find Out What Is Happening With Open Source Software Project

Nice idea.

Michael Ogawa has created some stunning visualizations for open source software projects such as Apache, Python, Eclipse IDE, and Postgres. From the project home page:

This visualization, called code_swarm, shows the history of commits in a software project. A commit happens when a developer makes changes to the code or documents and transfers them into the central project repository. Both developers and files are represented as moving elements. When a developer commits a file, it lights up and flies towards that developer. Files are colored according to their purpose, such as whether they are source code or a document. If files or developers have not been active for a while, they will fade away. A histogram at the bottom keeps a reminder of what has come before.

  • Code Swarm - An experiment in organic software visualization. (via Digg)

Download of the day: PostgreSQL 8.3 Database Server

PostgreSQL 8.3 has been released and available for download. PostgreSQL considered as the world's most advanced open source database. From the announcement page:

This release includes a record number of new and improved features which will greatly enhance PostgreSQL for application designers, database administrators, and users, with more than 280 patches by dozens of PostgreSQL contributors from 18 countries. Version 8.3 provides greater consistency of performance than previous versions, ensuring that every user can depend on the same high performance demonstrated in recent benchmarks for every transaction, whether in peak hours or not, seven days a week, 52 weeks per year. Major performance enhancements include:

-> Heap Only Tuples (HOT), which eliminate up to 3/4 of the maintenance overhead of frequently updated tables
-> Spread checkpoints and background writer autotuning, which reduce the impact of checkpoints on response times
-> Asynchronous commit option for much faster response times on some transactions

These changes also significantly accelerate transaction processing throughput, between 5% and 30%, depending on the workload.

Download PostgreSQL 8.3 Database Server

=> Visit official project web site to grab PostgreSQL 8.3 Database Server

You may find following document interesting while using PostgreSQL:

Postgresql 8.3 Commands Cheat Sheet

PostgreSQL is an object-relational database management system. This site has published excellent Postgresql 8.3 commands cheat sheet.

=> Download / View at Postgres onLine journal [ postgresonline.com ]

Download of the day: phpBB version 3 ( phpBB Gold )

phpBB final version 3 has been released and available for download. phpBB is a popular Internet forum package written in the PHP programming language.

New Features in phpBB3

* Modular design for the Admin Control Panel, Moderator Control Panel, and User Control Panel.
* Support for multiple database management systems, including MySQL, Microsoft SQL Server, Oracle, PostgreSQL, SQLite, and Firebird.
* Support for unlimited levels of subforums.
* Ability to create custom-defined BBCode.
* Ability to create custom profile fields.
* Greatly expanded permissions system.

From the announcement email:

The project has changed considerably since work on its second major release began. With a raft of new features requested by users and new ideas introduced by its developers, phpBB3 is more able than ever to support the new "social networking" trend. Security too has been a top priority with particular attention paid to reduce or eliminate the problems of the past. Indeed unusually for a project of this nature an independent security audit was performed to better ensure a safer future for its users.

So with great pride phpBB wishes to thank all of its developers, designers, team members and of course its community - please enjoy phpBB3.

Download phpBB version 3

=> Visit official web site to download phpBB stable version 3 (2.15 MiB)

How Do I Enable remote access to PostgreSQL database server?

By default, PostgreSQL database server remote access disabled for security reasons. However, some time you need to provide the remote access to database server from home computer or from web server.

Step # 1: Login over ssh if server is outside your IDC

Login over ssh to remote PostgreSQL database server:
$ ssh user@remote.pgsql.server.com

Step # 2: Enable client authentication

Once connected, you need edit the PostgreSQL configuration file, edit the PostgreSQL configuration file /var/lib/pgsql/data/pg_hba.conf (or /etc/postgresql/8.2/main/pg_hba.conf for latest 8.2 version) using a text editor such as vi.

Login as postgres user using su / sudo command, enter:
$ su - postgres
Edit the file:
$ vi /var/lib/pgsql/data/pg_hba.conf
$ vi /etc/postgresql/8.2/main/pg_hba.conf
Append the following configuration lines to give access to network:
host all all trust
Save and close the file. Make sure you replace with actual network IP address range of the clients system in your own network.

Step # 2: Enable networking for PostgreSQL

You need to enable TCP / IP networking. Use either step #3 or #3a as per your PostgreSQL database server version.

Step # 3: Allow TCP/IP socket

If you are using PostgreSQL version 8.x or newer use the following instructions or skip to Step # 3a for older version (7.x or older).

You need to open PostgreSQL configuration file /var/lib/pgsql/data/postgresql.conf or /etc/postgresql/8.2/main/postgresql.conf.
# vi /etc/postgresql/8.2/main/postgresql.conf
# vi /var/lib/pgsql/data/postgresql.conf
Find configuration line that read as follows:
Next set IP address(es) to listen on; you can use comma-separated list of addresses; defaults to 'localhost', and '*' is all ip address:
Or just bind to and IP address
Save and close the file. Skip to step # 4.

Step #3a - Information for old version 7.x or older

Following configuration only required for PostgreSQL version 7.x or older. Open config file, enter:
# vi /var/lib/pgsql/data/postgresql.conf
Bind and open TCP/IP port by setting tcpip_socket to true. Set / modify tcpip_socket to true:
tcpip_socket = true
Save and close the file.

Step # 4: Restart PostgreSQL Server

Type the following command:
# /etc/init.d/postgresql restart

Step # 5: Iptables firewall rules

Make sure iptables is not blocking communication, open port 5432 (append rules to your iptables scripts or file /etc/sysconfig/iptables):

iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d  --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s --sport 5432 -d 0/0 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

Restart firewall:
# /etc/init.d/iptables restart

Step # 6: Test your setup

Use psql command from client system. Connect to remote server using IP address and login using vivek username and sales database, enter:
$ psql -h -U vivek -d sales

Further readings:

=> man page: pgsql and configuration file

Quickly Backup / dump MySql / Postgres database to another remote server securely

Using UNIX pipe concept one can dump database to another server securely using ssh protocol. All you need remote execution rights for the 'dd' command, over SSH. This allows you to run database dumps across an encrypted channel.

Dump Postgres Database using ssh

Use pg_dump command command:
pg_dump -U USERNAME YOUR-DATABASE-NAME | ssh user@remote.server.com "dd of=/pgsql/$(date +'%d-%m-%y')"

Dump MySQL Database using ssh

Type the following command:
mysqldump -u USERnAME -p'PASSWORD' YOUR-DATABASE-NAME | ssh user@remote.server.com "dd of=/mysql/$(date +'%d-%m-%y')"