Database creation. Installation and initial setup of PostgreSQL

After you have downloaded English keyword database, you need to install a database server locally and learn how to make selections. To do this, we offer step-by-step instructions.

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; 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 base PostgreSQL data 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.

The first way to check if you have access to the database server is to try to create a database. A running PostgreSQL server can manage multiple databases, allowing you to create separate databases for different projects and users.

Your administrator may have already created a database for you and given you its name. In this case, you can skip this step and move on to the next section.

To create a database, named mydb in this example, run next command:

$createdb mydb

If you do not see any messages, the operation was successful and you can skip continuing this section.

If you see a message like:

Createdb: command not found

This means PostgreSQL was not installed correctly. Either it is not installed at all, or its directory is not included in the shell command search path. Try calling the same command by specifying absolute path:

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

This path may be different for you. Contact your administrator or check the installation instructions were followed to correct the situation.

Another possible answer:

Createdb: failed to connect to the postgres database: failed to connect to the server: No such file or directory Is it really running locally and accepting connections through the domain socket "/tmp/.s.PGSQL.5432"?

This means the server is down or createdb can't connect to it. In this case, review the installation instructions or contact your administrator.

You may also receive the following message:

Createdb: failed to connect to postgres database: IMPORTANT: role "joe" does not exist

where your username appears. This indicates that the administrator has not created a PostgreSQL account for you. (PostgreSQL accounts are different from operating system user accounts.) If you are an administrator yourself, read Chapter 20 for instructions on how to create accounts. To create a new user, you must become the operating system user under which PostgreSQL was installed (usually postgres). It is also possible that you have been assigned a name PostgreSQL user, which does not match your name in the OS; in this case you need to explicitly specify your PostgreSQL username using the -U switch or setting the PGUSER environment variable.

if you have Account user but does not have rights to create a database, you will see the message:

Createdb: Database creation failed: ERROR: No rights to create database

Not all users are allowed to create databases. If PostgreSQL refuses to create databases for you, then you need permission to do so. In this case, contact your administrator. If you installed PostgreSQL yourself, then for the purposes of this introduction you should be logged in as the user running the database server.

You can also create databases with other names. PostgreSQL allows you to create as many databases as you like. Database names must begin with a letter and be no longer than 63 characters. It is convenient to use your current username as the database name. Many utilities assume this name by default, so you can simplify entering commands. To create a database with this name, simply enter:

$createdb

If you no longer want to use your database, you can delete it. For example, if you are the owner (creator) of the mydb database, you can destroy it by running the following command:

$dropdb mydb

(This command does not default to the current user's database name; you must specify it explicitly.) This will physically delete all files associated with the database, and since this action cannot be undone, do not perform it without thinking about the consequences.

System Administration

This post is brief instructions for beginners, for those who installed PostgreSQL for the first time. Everything is here necessary information to get started with PostgreSQL.

Connecting to the DBMS

The first thing to do is to gain access to PostgreSQL, access as a superuser.
Authentication settings are located in the pg_hba.conf file.

local all postgres peers

This line indicates that the postgres user can connect to any local PostgreSQL database via a socket. There is no need to enter a password; the operating system will transmit the username, and it will be used for authentication.
Let's connect:

$ sudo -u postgres psql postgres postgres

To be able to connect via the network, you need to add the line to pg_hdba.conf:

# TYPE DATABASE USER ADDRESS METHOD
hostssl all all 0.0.0.0/0 md5

Authentication method md5 means that you will have to enter a password to connect. This is not very convenient if you frequently use the psql console. If you want to automate some actions, the bad news is that psql does not accept a password as an argument. There are two ways to solve these problems: setting the appropriate environment variable and storing the password in a special .pgpass file.

Setting the PGPASSWORD environment variable

I’ll say right away that it’s better not to use this method, because some operating systems allow you to view ordinary users environment variables using ps. But if you want, you need to write in the terminal:

export PGPASSWORD=mypasswd

The variable will be available in the current session. If you need to set a variable for all sessions, then you need to add the line from the example to the .bashrc or .bash_profile file

Storing the password in the .pgpass file

If we are talking about Linux, then the file should be located in $HOME (/home/username). Only the owner (0600) must have write and read rights. You need to write lines like this to the file:

hostname:port:database:username:password

You can write “*” in the first four fields, which will mean no filtering (full selection).

Getting help information

\? - will display all available commands along with their brief description,
\h - will display a list of all available queries,
\h CREATE - will provide help for a specific request.

DBMS user management

How to get a list of PostgreSQL users? Or you can query the pg_user table.

SELECT * FROM pg_user ;

Creating a new PostgreSQL user

From shell psql this can be done using the CREATE command.

CREATE USER username WITH password "password" ;

Or you can use the terminal.

createuser -S -D -R -P username

You will be prompted to enter a password.

Changing the user password

ALTER USER username WITH PASSWORD "password" ;

Changing user roles

To give the user permission to create databases, run the following query:

ALTER ROLE username WITH CREATEDB ;

Database Management

Displaying a list of databases in the psql terminal: The same from the Linux terminal:

psql -l

Creating a database from psql (PostgreSQL Terminal)

CREATE DATABASE dbname OWNER dbadmin ;

Creation new base data using the terminal:

createdb -O username dbname;

Setting up access rights to the database

If the user is the owner of the database, then he has all rights. But if you want to give access to another user, you can do this using the GRANT command. The query below will allow the user to connect to the database. But don't forget about configuration file pg_hba.conf, it must also have the appropriate connection permissions.

GRANT CONNECT ON DATABASE dbname TO dbadmin ;

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.

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. To create a new database, called mydb in this example, you can use the following 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

where your login name is mentioned instead of joe. This message appears if the administrator has not created a user for you in PostgreSQL. (A PostgreSQL user is not an operating system user.) If you are an administrator, see Chapter 20 for information about creating a user. You will need to register with operating system 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 will physically delete all files associated with the specified database so that they cannot be recovered, so perform this operation with great care.