PostgreSQL with PHP on Ubuntu – LEPP stack

Well, after reading some debate on using PostgreSQL for a production work for heavy site with full-text search capability instead of combining MySQL and ElasticSearch, I decide to give it a try with my new early-start-up project. In this tutorial I will note necessary steps to install PostgreSQL with PHP on Ubuntu to create a LEPP (Linux – Nginx – PostgreSQL – PHP) stack 🙂

1. First, install PHP and MySQL in the guide Linux, Nginx, MySQL, PHP – LEMP Stack for Laravel on Ubuntu
2. Install PostgreSQL server and client:

   [bash]apt-get install postgresql postgresql-contrib postgresql-client[/bash]

3. Install PgAdmin to manage PostgreSQL easier:

   [bash]apt-get install pgadmin3[/bash]

   Remember that you will need a GUI to use PgAdmin, or do desktop forwarding to be able to view the GUI

4. Do basic configuration:
   1. Connect and change password for postgres user:

      [bash]sudo -u postgres psql postgres
      \password postgres
      \q[/bash]

   2. Create a first database for the user postgres:

      [bash]sudo -u postgres createdb myfirstdb[/bash]

   3. Try logging in from command line as postgres user (remember that -h localhost is very important or you will get an error like psql: FATAL: Ident authentication failed for user “postgres”):

      [bash]psql -U postgres -h localhost[/bash]

5. Install php5-pgsql package:

   [bash]apt-get install php5-pgsql
   /etc/init.d/php5-fpm restart
   [/bash]

6. For Web-based management tool, we can simply use adminer at https://www.adminer.org/
7. Bonus: If you are migrating from MySQL, we can use some of migration tool from PostgreSQL website: Convert from other Databases to PostgreSQL.
   • I personally use MySQL2PostgreSQL due to its speed and simplicity
   • After converting, we will have a sql database which can be easily import into the PostgreSQL db with

     [bash]psql -h localhost -U postgres DATABASE_NAME < converted_file.sql[/bash]

   • Similar, we can backup a database in PostgreSQL with

     [bash]pg_dump -h localhost -U postgres DATABASE_NAME[/bash]

   • We can also dump in another host and restore in another host with pipe command as follows:

     [bash]pg_dump -h localhost -U postgres DATABASE_NAME | psql -h host2 dbname[/bash]

   • We can also use -Fc when dumping and restore later:

     [bash]PGPASSWORD="YourPasswordHERE" pg_dump -Fc -h localhost -U postgres DATABASE_NAME > DATABASE_NAME.dump
     pg_restore -h localhost -U postgres -d DATABASE_NAME DATABASE_NAME.dump[/bash]

Troubleshooting

• One day, you recognize that your disk space is fully consumed. Wtf? How can a 1G data size eat all of your 30G disk space? Well, in this case, be sure to check your VACUUM. However, don’t run VACUUM FULL as a periodic maintenance task. Instead, you should try running VACUUM for all tables separately. Use adminer tool to check for Index Length, Data Length to see if there is any problem there. Run REINDEX if necessary. In my case, I can free up to 85% of disk usage after running VACUUM on every tables.