Bitnami PostgreSQL

PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages).

How to connect to the PostgreSQL database?

You can connect to the PostgreSQL database from the same computer where it is installed with the psql client tool.

$ psql -U postgres

You will be prompted to enter the postgres user password.

The password will be the one entered during the installation process.

How to create a database backup?

To back up only the database, create a dump file using the pg_dump tool.

 $ pg_dump -U postgres DATABASE_NAME > backup.sql

This operation could take some time depending on the database size.

How to restore a database backup?

Once you have the backup file, you can restore it with a command like the one below:

$ psql -U postgres DATABASE_NAME < backup.sql

If you want to restore the database and the database schema does not exist, it is necessary to first follow the steps described below. As a side note, the value for the BITNAMI_USER_PASSWORD placeholder is included in the application credentials or, if the credentials were defined by the user, it is the same as the application password.

$ psql -U postgres
drop database DATABASE_NAME;
create database DATABASE_NAME;
create user USER_NAME;
alter role USER_NAME with password 'BITNAMI_USER_PASSWORD';
grant all privileges on database DATABASE_NAME to USER_NAME;
alter database DATABASE_NAME owner to USER_NAME;
$ psql -U postgres DATABASE_NAME < backup.sql

Note that the steps previously described will only back up the data contained inside your database. There may be other files that you should take into account when performing a full backup, such as files that may have been uploaded to the application. These files are stored in the application folder itself, so copy this folder to have a backup of your uploaded files.

How to change the PostgreSQL root password?

You can modify the PostgreSQL password using the following command at the shell prompt:

$ psql -U postgres
postgres=# alter user postgres with password 'NEW_PASSWORD';
postgresl=# \q

How to reset the PostgreSQL password?

If you don't remember your PostgreSQL database password, you can follow the steps below to reset it to a new value:

  • Change the authentication method in the configuration file at installdir/postgresql/data/pg_hba.conf from md5 to trust and reload the configuration.

     $ sudo sed -ibak 's/^\([^#]*\)md5/\1trust/g' installdir/postgresql/data/pg_hba.conf
     $ sudo -u postgres pg_ctl reload
    
  • Connect to the PostgreSQL database and set the password to a new value:

     $ psql -U postgres
     postgres=# alter user postgres with password 'NEW_PASSWORD';
     postgresl=# \q
    
  • Finally, change the authentication method back to md5 and reload the old PostgreSQL configuration:

     $ sudo sed -i 's/^\([^#]*\)trust/\1md5/g' installdir/postgresql/data/pg_hba.conf
     $ sudo -u postgres pg_ctl reload
    

You should now be able to connect to PostgreSQL with the new password.

How to connect to PostgreSQL from a different machine?

To connect to PostgreSQL from a different machine, you must make sure that port 5432 is opened for remote access or open it by following the instructions given in the FAQ.

How to create a database for a custom application?

These are the basic steps to create a new database and user for your applications:

  • Create a new role by executing the createuser command. With the options below, the new role will not be a superuser and will not have privileges for creating new databases or new roles (this is usually the default for the createuser command).

     createuser -U postgres USER_NAME -S -D -R -P
    

    You will be prompted to enter first the password for the new role and to reenter it, and then to enter the postgres role password.

  • Create a new database with the new role as the owner:

     createdb -U postgres DATABASE_NAME  -O USER_NAME
    

How to fix the PostgreSQL error on OS X 10.11 El Capitan?

IMPORTANT: If you are using OS X and you upgraded to OS X 10.11 "El Capitan", perform the following steps in order to continue using PostgreSQL. This is required because of the new System Integrity Protection feature.

Follow these steps:

  • Navigate to your stack installation directory (eg. /Applications/mappstack-VERSION/) and stop the servers using the graphical manager.

  • Rename the postgres binary to postgres.bin. When using the terminal, this is the command:

     $ mv postgresql/bin/postgres postgresql/bin/postgres.bin
    
  • Create a new file in the same location named postgres with the following content:

     #!/bin/sh
    
     . /Applications/mappstack-VERSION/scripts/setenv.sh
    
     exec /Applications/mappstack-VERSION/postgresql/bin/postgres.bin "$@"
    
  1. Change the permissions of the new file:

     $ chmod 755 postgresql/bin/postgres
    
  2. Restart the server using the graphical manager.

How to install PostGIS in a PostgreSQL database?

PostGIS adds support for geographic objects to the PostgreSQL object-relational database.

Linux

NOTE: Since Bitnami LAPP Stack v5.4.0-1 version, PostGIS is already included in the stack so it is not necessary to install it manually.

Follow these steps to install the PostGIS extension in a Bitnami LAPP Stack:

  • Install the compilation tools:

     $ sudo apt-get update
     $ sudo apt-get install build-essential libtool autoconf unzip wget 
    
  • Download and install the dependencies:

     $ wget http://download.osgeo.org/geos/geos-3.3.2.tar.bz2
     $ bunzip2 geos-3.3.2.tar.bz2
     $ tar -xvf geos-3.3.2.tar
     $ cd geos-3.3.2
     $ ./configure
     $ make 
     $ sudo make install
    
     $ wget http://download.osgeo.org/proj/proj-4.8.0.tar.gz
     $ tar -xzvf proj-4.8.0.tar.gz
     $ cd proj-4.8.0
     $ ./configure
     $ make
     $ sudo make install
    
  • Download and install PostGIS.

     $ wget http://postgis.refractions.net/download/postgis-1.5.3.tar.gz
     $ tar -xzvf postgis-1.5.3.tar.gz
     $ cd postgis-1.5.3
     $ ./configure
     $ make
     $ sudo make install
    
  • Verify that the postgis database has been successfully created, using the phpPgAdmin application at http://127.0.0.1/phppgadmin. For more information on using phpPgAdmin, refer to this page.

Learn more about configuring PostgreSQL with PostGIS.

Windows

NOTE: Since Bitnami WAPP Stack v5.4.0-1, PostGIS is already included in the stack.

Follow these steps to install the PostGIS extension in a Bitnami WAPP Stack:

  • Download and install the Bitnami WAPP Stack on your computer.

  • Download PostGIS for Windows. The version of PostGIS must be compatible with the version of PostgreSQL installed with the Bitnami WAPP Stack.

  • Specify the PostgreSQL installation directory, which is "C:\Program Files\Bitnami WAPP Stack\postgresql" by default.

  • When prompted for the password, enter the same password that you specified during the Bitnami WAPP Stack installation

  • Accept default values and enable the shp2pgsql loader in PgAdmin III.

  • Verify that the postgis database has been successfully created, using the phpPgAdmin application at http://127.0.0.1/phppgadmin. For more information on using phpPgAdmin, refer to this page.

Learn more about configuring PostgreSQL with PostGIS.

How to secure your server from remote connections?

Once you have created a new database and user for your application, connect your applications to the PostgreSQL server using that database and password.

It is strongly recommended that you do not have empty passwords for any user accounts when using the server for any production work.

Allow remote access only from the database user associated with the database name by editing the pg_hba.conf file as explained below.

  • Comment out the line that allow remote access to all users:

     #host all all all md5
    
  • Add a line for the new user:

     host DATABASE_NAME USER_NAME all md5
    
  • If your application will be connected from a fixed domain or IP address, you should update your pg_hba.conf file to only accept connections from that server instead:

     host DATABASE_NAME USER_NAME applicationserver.domain.com md5
    

    or

     host DATABASE_NAME USER_NAME IP_ADDRESS 255.255.255.255 md5
    
  • Change your postgres user password as explained in this section.

What is the default configuration?

The default configuration consists of:

  • A database cluster or directory under which all data will be stored. The default data directory in Bitnami is located at installdir/postgresql/data.
  • Every connection to the PostgreSQL database server is made using the name of some particular role. A database superuser bypasses all permission checks, except the right to log in. Bitnami configures a default superuser role with a name of postgres. The postgres role has remote access to the database.

Check our recommendations for a production server.

PostgreSQL version

In order to see which PostgreSQL version are your machine running, execute the following command:

$ postgres --version

PostgreSQL configuration files

The PostgreSQL configuration settings file is located at installdir/postgresql/data/postgresql.conf.

The PostgreSQL client authentication configuration file is located at installdir/postgresql/data/pg_hba.conf.

The PostgreSQL official documentation has more details on how to configure the PostgreSQL database.

PostgreSQL socket

On Unix, PostgreSQL clients can connect to the server using an Unix socket file. Usually when you use a PostgreSQL client tool included in the Stack, you will not need to specify the socket for the connection.

The socket file is created at installdir/postgresql/.s.PGSQL.5432.

PostgreSQL port

The default port in which PostgreSQL listens is 5432.

PostgreSQL log file

The main PostgreSQL log file is created at installdir/postgresql/data/postgresql.log file. You can change the default error reporting and logging configuration settings in the postgresql.conf file.

NOTE: On some platforms, you may need root account privileges to view these files.