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:
    apt-get install postgresql postgresql-contrib postgresql-client
  3. Install PgAdmin to manage PostgreSQL easier:
    apt-get install pgadmin3

    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:
      sudo -u postgres psql postgres
      \password postgres
      \q
    2. Create a first database for the user postgres:
      sudo -u postgres createdb myfirstdb
    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”):
      psql -U postgres -h localhost
  5. Install php5-pgsql package:
    apt-get install php5-pgsql
    /etc/init.d/php5-fpm restart
    
  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
      psql -h localhost -U postgres DATABASE_NAME < converted_file.sql
    • Similar, we can backup a database in PostgreSQL with
      pg_dump -h localhost -U postgres DATABASE_NAME
    • We can also dump in another host and restore in another host with pipe command as follows:
      pg_dump -h localhost -U postgres DATABASE_NAME | psql -h host2 dbname
    • We can also use -Fc when dumping and restore later:
      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

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.

Leave a Reply