Table of contents
- 1. Quick reference card
- 2. How to start/stop the PostgreSQL server?
- 3. What is the default configuration?
- 3.1.1. PostgreSQL socket
- 4. How to connect to the PostgreSQL database?
- 5. How to connect to the PostgreSQL database from a different machine?
- 6. How to create a database for a custom application?
- 7. How to change the PostgreSQL password?
- 8. How to reset the PostgreSQL password?
- 9. How to create a database backup?
- 10. How to restore a database backup?
- 11. How to install PostGIS in the PostgreSQL database on Windows?
- 12. How to install PostGIS in the PostgreSQL database on Linux?
- 13. How to debug errors?
Quick reference card
|Cloud Servers||Virtual Machines||Native Installers|
|UNIX socket||/opt/bitnami/postgresql/.s.PGSQL.5432||/opt/bitnami/postgresql/.s.PGSQL.5432||install_dir/postgresql/.s.PGSQL.5432 (Linux and Mac OS X)|
|System user account||postgres||postgres||postgres|
|Service control||sudo /opt/bitnami/ctlscript.sh start|stop|restart postgresql||sudo /opt/bitnami/ctlscript.sh start|stop|restart postgresql||install_dir/ctlscript.sh start|stop|restart postgresql|
How to start/stop the PostgreSQL server?
The native installers include a graphical tool to manage the servers easily. You can find the "manager-windows.exe", "manager-osx" or "manager-linux" tool in your installation directory. Using this tool, you can Start, Stop or Restart the servers and check the log files. You can click on the icon to start it.
On Windows: You can also start the Manager tool from shortcuts: Start -> Program Files -> Bitnami Stack -> Manager tool
Command line tool
If you prefer, you can use the "ctlscript.sh" utility from the command line. This script is in the installation directory.
On Virtual Machines and Cloud images:
$ sudo /opt/bitnami/ctlscript.sh start
A native installer on Linux:
$ cd ~/application-version $ ./ctlscript.sh start
On OS X: You can start the Manager tool from the installation directory or you can use the "ctlscript.sh" utility from a Terminal.
host:~ user$ cd /Applications/application-version host:~ user$ ./ctlscript.sh start
What is the default configuration?
Every connection to the PostgreSQL database server is made using the name of some particular role. The default configuration consists of:
- A superuser role with a name of 'postgres'. A database superuser bypasses all permission checks, except the right to log in. The initial password for this account is the same you specified in the setup wizard. For Virtual Appliances or AMIs the default password is "bitnami".
It is strongly recommended that you do not have empty passwords for any user accounts before using the server for any production work.
For security reasons, the PostgreSQL database does not let external connections by default. You only can connect from the local machine.
On Unix the PostgreSQL clients can connect to the server using an Unix socket file. In a BitNami Stack the default socket file is created in /installdir/postgres/.s.PGSQL.5432. Usually when you use a postgresql client tool included in the stack you will not need to specify the socket for the connection.
How to connect to the PostgreSQL database?
You can connect to PostgreSQL database from the same computer where it is installed.
- Open the BitNami console
- Run the postgresql client:
$ psql -U postgres
You can also run the postgreSQL client directly "/installdir/postgresql/bin/psql". If you have installed phpPgAdmin component, you can manage the PostgreSQL database from a browser. You can find more info at phpPgAdmin.
How to connect to the PostgreSQL database from a different machine?
First of all, you have to open the PostGreSQL port in your machine. It is 5432 by default, but you can check it in installdir/bitnami/properties.ini file. To open this port, follow the instructions in our wiki:
After that, you have to change the following config files to make your PostgreSQL available remotely. Allow remote access to postgresql database
$ vim installdir/postgresql/data/pg_hba.conf
Now append the following line.
host all all ip_address net_mask auth_method
where "ip_address" means your IP (can be a network IP). "net_mask" means network mask. "auth_method" means authentication method. There are few authentication methods. such as "password, trust, md5". You should use md5 to use encrypted password authentication.
host all all 192.168.1.1 255.255.255.0 md5
Allow TCP/IP communication
$ vim installdir/postgresql/postgresql.conf
In the "listen_addresses" option you have to change the default value, "localhost", to '*' (or an IP if you prefer).
After that, restart the postgresql database
Now, you should be able to access to your PostgreSQL database from another machines.
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 executing the command createuser. 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 database_user -S -D -R -P
You will be prompted to enter first the password for the new role and to reenter it, and then you will be prompted to enter the 'postgres' role password.
Create a new database with the new role as the owner:
createdb -U postgres database_name -O database_user
How to change the PostgreSQL password?
These are the default credentials for the PostgreSQL database.
- username: postgres
- password: bitnami (for BitNami AMIs), <application password> (for BitNami Cloud Hosting and Native Installers)
You can change the "postgres" user password from the command line:
$ psql -U postgres postgres=# alter user postgres with password 'YOUR_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: (Assuming that the installation directory is /opt/bitnami)
Change the authentication method in the configuration file /opt/bitnami/postgresql/data/pg_hba.conf from 'md5' to 'trust' and reload the configuration. You can do this with the commands below:
sudo sed -ibak 's/^\([^#]*\)md5/\1trust/g' /opt/bitnami/postgresql/data/pg_hba.conf sudo -u postgres pg_ctl reload
You should be now able to connect to the PostgreSQL database and set the password to a new value:
psql -U postgres postgres=# alter user postgres with password 'YOUR_NEW_PASSWORD'; postgresl=# \q
Finally, change back the authentication method top md5 and reload the old PostgreSQL configuration:
sudo sed -i 's/^\([^#]*\)trust/\1md5/g' /opt/bitnami/postgresql/data/pg_hba.conf sudo -u postgres pg_ctl reload
You should now be able to connect to PostgrSQL with the new password.
How to create a database backup?
If you are interested in a backup of only the database data, you can 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 data size.
How to restore a database backup?
Once you have the backup file, you can restore it.
$ 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, you can find the value for BITNAMI_USER_PASSWORD below in the application configuration file. This depends on the application and you can find this information in the application page itself.
$ psql -U postgres drop database database_name; create database database_name; create user database_user; alter role database_user with password 'BITNAMI_USER_PASSWORD'; grant all privileges on database database_name to database_user; alter database database_name owner to database_user; $ psql -U postgres database_name < backup.sql
Note that the steps previously described will only back up the state of your database. Other files that you should take into account when performing a backup are files that have been uploaded to the application. These files are stored in the application folder itslef so you can copy this folder to have a backup of your uploaded files.
How to install PostGIS in the PostgreSQL database on Windows?
Since BitNami WAPP Stack version 5.4.0-1 version, PostGIS is already included in the stack.
PostGIS adds support for geographic objects to the PostgreSQL object-relational database.These are the basic steps to install this extension in a BitNami WAPP Stack.
- Download and install the BitNami WAPPStack on your computer.
- Download PostGIS for Windows: http://postgis.refractions.net/download/windows/
- PostGIS 1.5.3 release for PostgreSQL 9.1 download link: http://postgis.refractions.net/download/windows/pg91/postgis-pg91-setup-1.5.3-2.exe
Note: The version of PostGIS must be compatible with the version of PostgreSQL installed with WAPPStack. At the moment of writing this artivle
- Specify the PostgreSQL installation directory, "C:\Program Files\BitNami WAPPStack\postgresql" by default.
- When prompted the password, enter the same that you specified during the BitNami WAPPStack installation
- Accept defaults enable enable the "shp2pgsql" loader in PgAdmin III.
- Verify the "postgis" database has been successfully created. You can check it using the phpPgAdmin application at http://127.0.0.1/phppgadmin. The default user is "postgres" and the password is the same that you set during the WAPPStack installation.
How to install PostGIS in the PostgreSQL database on Linux?
Since BitNami LAPP Stack version 5.4.0-1 version, PostGIS is already included in the stack so it is not necessary to install it manually.
PostGIS adds support for geographic objects to the PostgreSQL object-relational database.These are the basic steps to install this extension in a BitNami LAPP Stack Ubuntu Virtual Appliance or AMI. Notice that the versions could be different.
$ 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 This could take some time $ 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
Now download and install the 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
You can find more info about how to configure the PostgreSQL database using PostGIS at http://postgis.refractions.net/docs/ch02.html
How to debug errors?
The main PostgreSQL log file is created at /installdir/postgresql/postgresql.log file.