Basic postgresql server setup

Jack Wallen
Nov 10, 2009
Updated • Nov 28, 2012
Linux
|
5

So many tool require databases. If you are a web administrator or a company with large stores of information, then you know the importance of databases. One of the most oft-used databases available is MySQL. But that is not the only player on the court. Another cross platform object-relational database management tool is PostgreSQL. Many people refer to PostgreSQL as the Oracle of the open source world. That is because PostgreSQL is dense with features but not as fast as MySQL. And where MySQL is a simple to use database management tool, PostgreSQL is often seen as overly complicated.

Of course there are variations on that opinion. But that is neither here nor there. The purpose of this tutorial is to help you get a PostgreSQL server up and running quickly and easily. To make this simple we will make this a part of our Ubuntu Server series, so all you have to do is have your Ubuntu Server up and running (see my article "Installing Ubuntu Server 9.04" to get started.) Once you have that server up and running you are ready to get your PostgreSQL server up.

Installation

The first thing you need to do is to install the necessary software. Since this is Ubuntu, it's quite easy. Open up a terminal window and issue the command:

sudo apt-get install postgresql

Once the software is installed you are ready to set it all up.

Change the default user password

One of the first steps you want to take is to change the default password for the user postgres. Sine we are using Ubuntu you will have to use the sudo command to change to the postgres user like so:

sudo su - postgres

You will have to enter your sudo password after which you will now be issuing commands as the user postgres. The next step is to gain access to the postgresql command prompt with the command:

psql

Your new command prompt will look like:

postgres=#

NOTE: The only user that can open the PostgreSQL prompt without defining a database to work with is the user postgres. Other users would have to gain access to the command prompt with a command like:

psql DB_NAME

Where DB_NAME is the name of an existing database.

Changing the password is as simple as issuing the command:

\password postgres

You will then be asked to enter a password and then verify that password.

Your default password has not been changed. You can exit from the PostgreSQL prompt by issuing the command:

\q

Create a database

Now, while still logged in as the postgres user, let's create a database. For this you do not have to be logged into the PostgreSQL command prompt. Instead just issue the command:

createdb testdb

Where testdb is the name of the database you want to create. To check to make sure that database was created go back to the PostgreSQL command prompt (remember, the command psql) and enter:

\l

You should see a listing for your new database like:

testdb | postgres | UTF8 | en_US.UTF-8  | en_US.UTF-8

Once again, log out of the PostgreSQL command prompt with the command:

\q

Create a user

By default, the only user that can connect to a database is the postgres user. This will be of no help when you need to connect with another user. To create a new user (that can connect to databases) you would issue the command (as the user postgres):

createuser --superuser USERNAME

Where USERNAME is the name of the user you want to create.

Final thoughts

Now you should have a basic PostgreSQL installation with a test database and a user, besides postgres, that can work with the tools. Next time we work with PostgreSQL we'll discuss more challenging issues with this outstanding database tool.

Advertisement

Previous Post: «
Next Post: «

Comments

  1. Caleb Cushing ( xenoterracide ) said on November 11, 2009 at 4:33 am
    Reply

    Also you shouldn’t give your newly created user the superuser rights. that’s the same as logging in as the postgres user, which for your database is the equivalent of being root on your box on the time, but having given uid 0 a different name.

  2. Caleb Cushing ( xenoterracide ) said on November 11, 2009 at 4:21 am
    Reply

    “NOTE: The only user that can open the PostgreSQL prompt without defining a database to work with is the user postgres. Other users would have to gain access to the command prompt with a command like:”

    that’s simply not true. psql by default assumes that the username and the database name that you are trying to connect to is the same as that of your system account. the default postgres username and database is postgres.

    If I as xenoterracide create a database named xenoterracide owned by a username xenoterracide then all I have to do from a prompt (if the db is on localhost) is type psql.

    see my post on this subject here http://xenoterracide.blogspot.com/2009/03/postgresql-initial-setup-users-and.html and more on hba here http://xenoterracide.blogspot.com/2009/08/postgresql-initial-setup-authentication.html

  3. Grant said on November 11, 2009 at 1:04 am
    Reply

    One more thing you will likely need to do:

    Look at the file pg_hba.conf. It is probably in /etc/postgresql. It says who is allowed to connect, and how.

Leave a Reply

Check the box to consent to your data being stored in line with the guidelines set out in our privacy policy

We love comments and welcome thoughtful and civilized discussion. Rudeness and personal attacks will not be tolerated. Please stay on-topic.
Please note that your comment may not appear immediately after you post it.