PostgreSQL/Installing and Configuring PostgreSQL

From Wikibooks, open books for an open world
Jump to: navigation, search

Installing[edit]

Installing from packages[edit]

sudo apt-get install postgresql.

Installing from source[edit]

Download from http://www.postgresql.org/download/.

Firewall[edit]

Once installed, PostgreSQL listen to the port 5432. So the firewall might need an exception to allow connections, even from localhost.

Starting and stopping[edit]

Linux[edit]

$ /etc/init.d/postgresql start

Windows[edit]

By default, PostgreSQL launches at each reboot so it can consume too many resources. To avoid that, just execute services.msc and set the PostgreSQL service in manual start.

Then, create a file PostgreSQL.cmd containing:

net start postgresql-x64-9.5
pause
net stop postgresql-x64-9.5

As long as this script is launched as an administrator, the database will work. Just press a key to shutdown it after utilization.

Creating a user[edit]

The following command creates a new user with superuser privileges:

$ createuser -U postgres -s <some username>

Creating a new user is a database operation, so it can only be done by an existing database user. You need to specify which database user to use (otherwise it will default to using a database user with the same name as your current UNIX user, which is unlikely to be correct). Default installs typically have a user called postgres that can be used for this.

To set a password for the newly created user, log in to the database using an account with superuser privileges, and issue the command:

ALTER USER <username> WITH ENCRYPTED PASSWORD 'secret';