Setup PostgreSQL

PostgreSQL is a free and open source database which is used in demanding production environments, for example, to store the .org domain name database, and has been proven to scale well into hundreds of terabytes of data. It has very fast and solid transaction support, and provides an auto-vacuum feature that frees the administrator from most database maintenance tasks.

On an Ubuntu or other Debian-based Linux distribution, it is easy to install PostgreSQL and its Python API with:

1 sudo apt-get -y install postgresql

2 sudo apt-get -y install python-psycopg2

It is wise to run the web server(s) and the database server on different machines. In this case, the machines running the web servers should be connected with a secure internal (physical) network, or should establish SSL tunnels to securely connect with the database server.

Start the database server with:

1 sudo /etc/init.d/postgresql restart

When restarting the PostgreSQL server, it should notify which port it is running on. Unless you have multiple database servers, it should be 5432.

The PostgreSQL configuration file is:

1 /etc/postgresql/x.x/main/postgresql.conf

(where x.x is the version number). The PostgreSQL logs are in:

1 /var/log/postgresql/

Once the database server is up and running, create a user and a database so that web2py applications can use it:

1 sudo -u postgres createuser -P -s myuser

2 createdb mydb

3 echo 'The following databases have been created: '

5 psql mydb

The first of the commands will grant superuser-access to the new user, called myuser. It will prompt you for a password.

Any web2py application can connect to this database with the command:

1 db = DAL( "postgres://myuser:[email protected]:5432/mydb")

where mypassword is the password you entered when prompted, and 5432 is the port where the database server is running.

Normally you use one database for each application, and multiple instances of the same application connect to the same database. It is also possible for different applications to share the same database.

For database backup details, read the PostgreSQL documentation; specifically the commands pg.dump and pg.restore.

Was this article helpful?

0 0

Post a comment