PostgreSQL with PHP on Ubuntu – LEPP stack

postgresql-logoWell, 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

    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

  6. For Web-based management tool, we can simply use adminer at
  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]


  • 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.

About NhocConan

A super lazy guy who tries to write tech blog entries in English.He is lazy, so he can only write when he is in a good mood or when he is tired of coding.

Leave a comment

Your email address will not be published. Required fields are marked *