Basic postgresql server setup - gHacks Tech News

Basic postgresql server setup

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.





  • We need your help

    Advertising revenue is falling fast across the Internet, and independently-run sites like Ghacks are hit hardest by it. The advertising model in its current form is coming to an end, and we have to find other ways to continue operating this site.

    We are committed to keeping our content free and independent, which means no paywalls, no sponsored posts, no annoying ad formats (video ads) or subscription fees.

    If you like our content, and would like to help, please consider making a contribution:

    Comments

    1. 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.

    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. 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.

    Leave a Reply