Menu
homeTechnique

Installation and initial configuration of PostgreSQL. Database creation

PostgreSQL is a cross-platform object-relational DBMS with open source code. This article will show you how to install PostgreSQL in Ubuntu Linux, connect to it and run a couple of simple SQL queries, as well as how to set up a backup.

To install PostgreSQL 9.2 on Ubuntu 12.10, run the following commands:

sudo apt-add-repository ppa:pitti/postgresql
sudo apt-get update
sudo apt-get install postgresql-9.2

Let's try to work with the DBMS through the shell:

sudo -u postgres psql

Let's create a test database and a test user:

CREATE DATABASE test_database;
CREATE USER test_user WITH password "qwerty" ;
GRANT ALL ON DATABASE test_database TO test_user;

To exit the shell, enter the command \q .

Now let's try to work with the created database on behalf of test_user:

psql -h localhost test_database test_user

Let's create a new table:

CREATE SEQUENCE user_ids;
CREATE TABLE users (
id INTEGER PRIMARY KEY DEFAULT NEXTVAL ("user_ids" ) ,
login CHAR(64) ,
password CHAR(64));

Please note that, unlike some other DBMSs, PostgreSQL does not have columns with the auto_increment property. Instead, Postgres uses sequences. On this moment It’s enough to know that using the nextval function we can get unique numbers for a given sequence:

SELECT NEXTVAL ("user_ids" ) ;

By setting the default value for the id field of the users table to NEXTVAL("user_ids"), we have achieved the same effect that auto_increment gives. When adding new records to the table, we don't have to specify the id, because a unique id will be generated automatically. Multiple tables can use the same sequence. This way we can guarantee that the values ​​of some fields in these tables do not overlap. In this sense, sequences are more flexible than auto_increment.

The exact same table can be created using just one command:

CREATE TABLE users2 (
id SERIAL PRIMARY KEY ,
login CHAR(64) ,
password CHAR(64));

In this case, the sequence for the id field is created automatically.

Now using the \d command you can see a list of all available tables, and using \d users you can see a description of the users table. If you don't get the information you're looking for, try \d+ instead of \d . You can get a list of databases with the \l command, and switch to a specific database with the \c dbname command. To display command help, say \?

.

It's important to note that PostgreSQL converts table and column names to lowercase by default. If you don't want this behavior, you can use double quotes:

CREATE TABLE "anotherTable" ("someValue" VARCHAR (64 ) ) ;

Another feature of PostgreSQL that may cause difficulties when starting to work with this DBMS is the so-called “schemas”. A schema is something like a namespace for tables, like a directory with tables inside a database.

Creating a schema:

CREATE SCHEMA bookings;

Switch to scheme:

SET search_path TO bookings; View list existing schemes

you can use the \dn command. The default schema is named public. In principle, you can successfully use PostgreSQL without knowing about the existence of schemas. But when working with legacy code, and in some edge cases, knowing about schemas can be very useful.

Otherwise, working with PostgreSQL is not much different from working with any other relational DBMS:
INSERT INTO users (login, password)
VALUES ("afiskon" , "123456" ) ;

SELECT * FROM users;

If you now try to connect to Postgres from another machine, you will fail:

psql -h 192.168.0.1 test_database test_user
Psql: could not connect to server: Connection refused
Is the server running on host "192.168.0.1" and accepting

TCP/IP connections on port 5432?

To fix this, add the line:

listen_addresses = "localhost,192.168.0.1"

...to the /etc/postgresql/9.2/main/postgresql.conf file as well.

1. Installation

1.1. Installation from the official repository If the latest available one is important to you PostgreSQL version

(if not, I advise you to think carefully), then you need to install it from the official PostgreSQL repository. This can be done by following the official instructions. Then, you should update the packages:

$ sudo apt-get update

And install PostgreSQL with the command:

  • $ sudo apt-get install postgresql-x.x x.x

- required version List of all available versions

can be viewed with the command:

$ sudo apt-cache search postgresql

Installing PostgreSQL from the OS repository is done by adding two main packages:

$ sudo apt-get install postgresql postgresql-contrib

2. PostgreSQL console

All available operations on databases and users are performed from the console psql.

2.1. Login to the console

First you need to log in as a user postgres, this is only possible with rights root:

# su - postgres

User postgres- this is a kind of superuser for the database PostgreSQL data. Then, from under the user postgres you can log into the console:

Or easier, just log into the console psql under user postgres:

$ sudo -u postgres psql

2.2. Quitting the console

When all operations on PostgreSQL users and databases have been completed in the psql console, it is not immediately possible to figure out how to exit it. Everything is very simple here:

Postgres=#\q

And, if necessary, we leave the user postgres:

3. PostgreSQL users

3.1. Creating a user

Everything is quite simple here:

# CREATE USER username WITH PASSWORD "12345";

  • username- new user login
  • ‘12345’ - User password. Entered in quotation marks

3.2. Deleting a user

It's even simpler here:

# DROP USER username;

  • username- login of the user who needs to be deleted.

4. Databases

All manipulations with the database are also performed in the console psql.

4.1. Database creation

Everything here is the same as when creating a user:

# CREATE DATABASE dbname;

4.2. Delete a database

# DROP DATABASE dbname;
  • dbname- name of the database to be deleted

Please note that the database will refuse to be deleted in certain cases:

  • If there are users with rights to this database. Before deleting the database, their rights must be revoked. More on this later.
  • If there is at least one open connection session with the database. In this case, sessions will need to be closed. As a last resort, you can simply stop the servers interacting with this database at the time of its removal (although there is little point in starting them after deleting the database)

4.3. Assigning rights to users

Having a database and users in a PostgreSQL system by itself does not produce results. For correct operation specific user with a specific database, he needs to be assigned rights to work with it. To do this, run the command:

# GRANT ALL PRIVILEGES ON DATABASE dbname TO username;

  • dbname- name of the database to which access rights must be granted
  • username- the name of the user who will be granted rights over the specified database

4.4. Removing user rights

Sometimes, it becomes necessary to change the user managing the database, or simply revoke rights for its subsequent deletion. I recommend not to neglect this command and act according to the principle “One user manages one database.”

The first test to show that you can access the database server is to try to create a database. Running server PostgreSQL can manage multiple databases. Typically, for each project or each user it is used separate base data. It is possible that your machine's administrator has already created a database for you. It should have told you the name of your database. In this case, you can skip this section and move on to the next one. For creating new base data, in this example called mydb , you can use

next command : $createdb mydb If the command completed without any messages, then

this step was passed successfully and you can skip what is written below until the end of this section.

If you get something similar to: createdb: command

not found

this means that the PostgreSQL product was not installed correctly. Either not everything was installed or the search path in your shell was not set appropriately. Try calling this command using the full path:

$ /usr/local/pgsql/bin/createdb mydb

The full path on your machine may be different. Contact your administrator or check the installation instructions to correct the situation.

Another message you may receive: createdb: could not connect to database postgres: could not connect to server: No such file or directory No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/. s.PGSQL.5432"?

This means that the server was not started, or that it was not started as expected by the createdb command. Again, check the installation instructions or consult your administrator. Another message you may receive: createdb: could not connect to database postgres: FATAL: user "joe" does not exist under the name of the user under which PostgreSQL is running (usually postgres) to create the first user in the DBMS.

For the create database command, you can also specify a username in PostgreSQL that is different from your current operating system username; in this case you need to set the PostgreSQL username using the -U option or setting the PGUSER environment variable. If desired user

exists but does not have the permissions required to create a database, you will see the following message: createdb: database creation failed: ERROR: permission denied to create database Not every user has authorization to create new databases. If PostgreSQL rejects your attempts to create databases, then your machine administrator will need to give you rights to create databases. Consult him in this case.

If you installed PostgreSQL yourself, then you should, for the purposes pursued by this

training manual connect as the user on whose behalf the database server is launched. You can also create databases with any other names.

PostgreSQL allows you to create any number of databases on a single server. Database names must consist of letters and numbers (there must always be a letter first) and be no more than 63 bytes in length. It is quite convenient to create a database with the same name as the user. Many tools will default to this as the database name, so you don't have to click too many buttons. To create such a database with the same name as the user, simply type: $ createdb If you do not want to use your database in the future, you can delete it. For example, if you are the owner (creator) of the mydb database, you can drop it using the following command: $ dropdb mydb (For this command, you must be sure to specify a database name; it will not assume that the database name is the current user's name.) This command

IMPORTANT: If you have no experience working with databases or this experience is minimal, then we strongly recommend starting with a minimum base to work out the process.

The screenshots in the instructions show working with a minimum base, and accordingly the time is indicated for a minimum base, but working with an extended and maximum base will take longer.

Downloading and installing the PostgreSQL database server

http://www.postgresql.org/download/windows/

1. Download free program for the PostgreSQL database from the official website http://www.postgresql.org/
download/windows/

http://www.enterprisedb.com/products-services-training/pgdownload#windows:

Then follow the link to detailed page database versions for different OS: http://www.enterprisedb.com/
products-services-training/
pgdownload#windows :


IMPORTANT: We recommend installing the database server and making selections in 64-bit system with at least 6 GB random access memory when working with minimal and extended databases. In the case of a maximum base, the recommended amount of RAM is from 32 GB, also desirable fast disk. On a computer with a 32-bit system, the process of importing data, indexing it, and actually retrieving it will take a very, very long time, so it is better to abandon this idea.

If you don’t know the bit depth of your OS, you can look it up in the computer properties ( right button mouse on the “Computer” item, select “Properties” from the menu):


2. Install the downloaded PostgreSQL database. During installation, almost everything is by default...


...except for a few things.

3. We start the table optimization operation in a similar way:

vacuum "eng_data_table";


Optimization takes on average 15-30 minutes.

Note: We perform requests one at a time, first erasing previous requests that have already been executed. You can find out whether the request was completed or not from the message in the Output Pane at the bottom (it appears when the request is completed), and you can find out the execution time in the status bar at the bottom of the window.