PostgreSQL Administration for MySQL Admins

http://olex.openlogic.com/wazi/2012/postgresql-administration-for-mysql-admins


When people think about deploying an open source web ecosystem, they often choose Linux, along with Apache, PHP, and the MySQL database. However, MySQL isn’t the only mature and feature-rich open source option; rival PostgreSQL can be an equally good choice. While the two are both relational databases, they differ when it comes to implementation details. But don’t worry – if you know how to manage MySQL, you can learn how to handle PostgreSQL fairly quickly.
PostgreSQL runs on all major operating systems, including Linux, FreeBSD, Solaris, Mac OS X, and Windows. It is ACID-compliant, highly scalable (there are live PostgreSQL systems with in excess of 4 terabytes of data), and offers database functions such as foreign keys, triggers, and stored procedures. It also has native APIs for C/C++, Java, .Net, and PHP.
One reason PostgreSQL may be more attractive for some organizations than MySQL is that it uses a different license. PostgreSQL’s license, like the BSD and MIT licenses, grants the right to use, copy, modify, and distribute the software as long as the relevant copyright notices are maintained. MySQL, meanwhile, is licensed under several licenses, open and commercial, including the GPL. MySQL is free to use under the GPL if and only if you are developing and distributing open source applications which themselves are under an open source license. In terms of freedom for the end user, PostgreSQL offers the better choice.

Getting Started

On a CentOS 6 system, installing PostgreSQL is straightforward using the yum command: yum install postgresql postgresql-server. However, unlike MySQL, you must initialize and configure the database after installation, which you can do with the command service postgresql initdb. After that you can start and stop the database server using the service command: service postgresql start.
MySQL installations have a root user, and one of the normal installation tasks is to configure the password for this user. PostgreSQL uses a different approach, as explained in the Client Authentication section in the PostgreSQL documentation. It allows for a variety of client authentication methods. You can configure the server to authenticate clients based on explicit trust, or using a password, or using a more advanced network authentication method such as LDAP or Kerberos. You can configure the different methods in the file /var/lib/pgsql/data/pg_hba.conf.
With the default authentication method, each user needs to be added to the internal PostgreSQL users database. You can do this as the default user, postgres, which is like the root user under MySQL. First become superuser (su -), then switch to the postgres user with the command su – postgres. You can then use the PostgresSQL createuser command to add another user:

$ createuser joe
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n

Under MySQL you can use the mysqladmin command for most administration tasks. PostgreSQL, by constrast, provides a number of native commands for administration, such as createdb, dropdb, and createuser. A user who has previously been added to the internal users database using the createuser command can enter createdb at the Linux command prompt to create a database with the same name as their username. createdb mydb can be used to create a database called mydb, and so on. To delete a database, use the dropdb command.
Where you use mysql to enter the MySQL environment, you use psql with PostgreSQL, but the two environments differ in many ways. To get help from within the PostgreSQL shell, use \h, and to quit use \q. To see a list of users created with the createuser command, use \du. To see a list of databases, use \l. To find out which tables exist, use \d.
Standard SQL commands such as SELECT current_date; and SELECT version() work equally well in MySQL and PostgreSQL. The SQL commands to create a table, insert a row, and query the PostgreSQL database will be familiar to any MySQL administrator. Here is a small set of SQL commands that work equally on MySQL and PostgreSQL (with the output shown from PostgreSQL):

CREATE TABLE birthdays (
firstname varchar(80),
surname varchar(80),
dob date
);

INSERT INTO birthdays (firstname, surname, dob) VALUES ('Fred', 'Smith', '1989-05-02');

SELECT * FROM birthdays;

 firstname | surname |    dob
-----------+---------+------------
 Fred      | Smith   | 1989-05-02
(1 row)

A well-used tool in the MySQL admin’s toolbox is the mysqldump command, which dumps a database, as SQL, into a text file. PostgreSQL has a similar command called pg_dump. Like the MySQL equivalent, it takes a database name as the first parameter (otherwise the username is assumed as the database name), but to dump a certain table you must use the -t parameter:

$ pg_dump -t birthdays joe

--
-- Name: birthdays; Type: TABLE; Schema: public; Owner: joe; Tablespace:
--

CREATE TABLE birthdays (
    firstname character varying(80),
    surname character varying(80),
    dob date
);

...

The output, of course, can be redirected to a file using > or the -f option.
pg_dump dumps the contents of a single database, and it does not include information about the database users. To dump the entire contents of all the databases, use pg_dumpall, which is akin to the MySQL mysqldump –all-databases command.
Most MySQL administrators are familiar with the phpMyAdmin web-based administration tool. The equivalent for PostgreSQL is phpPgAdmin. It is open source, under active development (the most recent release was in March 2012), and supports PostgreSQL 7.4.x, 8.0.x, 8.1.x, 8.2.x, 8.3.x, 8.4.x, and 9.0.x.
Since PostgreSQL comes with all the command-line tools needed to deploy it on a server and offers APIs for a variety of programming languages, it is a good choice when you want to develop a new application that needs a database back end. However, some existing third-party web applications lack support for PostgreSQL. WordPress, for instance, remains firmly a MySQL-only product, but other popular web platforms, such as Joomla and Drupal, support PostgreSQL (from version 2.5 and 7 respectively).
Speaking of versions, like all active products, PostgreSQL is always being improved with new features. The current version shipping with CentOS 6 is PostgreSQL 8.4, which will be maintained by the PostgreSQL team until July 2014. The latest stable release is 9.1, which is earmarked for end of life in 2016. PostgresSQL 9.1 adds new features such as synchronous replication and k-nearest-neighbor indexing. The PostgreSQL project provides RPM packages for 9.1 for RHEL 6, Scientific Linux 6, and CentOS 6 for organizations that wish to use the latest version.

Summary

PostgreSQL offers a worthy alternative to MySQL in both technical terms and in terms of its licensing model. If you want to deploy your own web platform, PostgreSQL might be the more advantageous choice, as it doesn’t use the GPL. For those using popular web applications like Joomla or Drupal, PostgreSQL can be used as a drop-in replacement. In terms of functionality and scalability, PostgreSQL is a serious contender.