PostgreSQL

Contents
	Introduction
	Install
	\l: List Existing DBs
	Create DB
	drop: Remove DB
	Check Encoding
	\c: Use DB
	Connect to DB
	Run script from file
	\dt: List Tables
	\d table_name: Table Contents
	Users
	Alter Table
	Import from .csv
	\q: Exit PostgreSQL
	Current Schema
	PostgreSQL script from Bash
	Env variables

Introduction

PostgreSQL also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance.

It was originally named POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California, Berkeley.

In 1996, the project was renamed to PostgreSQL to reflect its support for SQL. After a review in 2007, the development team decided to keep the name PostgreSQL and the alias Postgres.

PostgreSQL features transactions with Atomicity, Consistency, Isolation, Durability (ACID) properties, automatically updatable views, materialized views, triggers, foreign keys, and stored procedures.

It is designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users. It is the default database for macOS Server and is also available for Microsoft Windows , Linux , FreeBSD , and OpenBSD .

Install

Postinstall

After installation, check the version of the installed PostgreSQL

postgres -V

postgres (PostgreSQL) 9.2.24

Location setting files, for example, postgresql.conf can be found by running

-bash-4.2$ su - postgres -c "psql -c 'SHOW config_file;'"

Password: config_file ------------------------------------- /var/lib/pgsql/data/postgresql.conf (1 row)

In this example, the directory that contains the settings is

/var/lib/pgsql/data

It is useful to study its contents

ll /var/lib/pgsql/data/

total 48 drwx------. 7 postgres postgres 67 Jun 9 22:54 base drwx------. 2 postgres postgres 4096 Jun 9 23:19 global drwx------. 2 postgres postgres 18 Jun 9 13:54 pg_clog -rw-------. 1 postgres postgres 4371 Jun 10 01:23 pg_hba.conf -rw-------. 1 postgres postgres 1636 Jun 9 13:54 pg_ident.conf drwx------. 2 postgres postgres 58 Jun 10 00:00 pg_log drwx------. 4 postgres postgres 36 Jun 9 13:54 pg_multixact drwx------. 2 postgres postgres 18 Jun 9 14:14 pg_notify drwx------. 2 postgres postgres 6 Jun 9 13:54 pg_serial drwx------. 2 postgres postgres 6 Jun 9 13:54 pg_snapshots drwx------. 2 postgres postgres 25 Jun 10 02:06 pg_stat_tmp drwx------. 2 postgres postgres 18 Jun 9 13:54 pg_subtrans drwx------. 2 postgres postgres 6 Jun 9 13:54 pg_tblspc drwx------. 2 postgres postgres 6 Jun 9 13:54 pg_twophase -rw-------. 1 postgres postgres 4 Jun 9 13:54 PG_VERSION drwx------. 3 postgres postgres 60 Jun 9 13:54 pg_xlog -rw-------. 1 postgres postgres 19889 Jun 10 01:43 postgresql.conf -rw-------. 1 postgres postgres 45 Jun 9 14:14 postmaster.opts -rw-------. 1 postgres postgres 92 Jun 9 14:14 postmaster.pid

Connect to DB

To access databases, you can use the standard psql console.

Windows:

If you want to connect from bash in windows don't forget to add a location psql.exe (I have this C:\Program Files\PostgreSQL\12\bin) in PATH

How this is done is described in the article PATH

psql.exe -h localhost -p 5433 -U postgres

Linux:

sudo su - postgres
psql

psql (12.7 (Ubuntu 12.7-0ubuntu0.20.04.1)) Type "help" for help.

For DB on localhost

psql -h 127.0.0.1 -d DATABASENAME -U DATABASEUSERNAME

Get connection info

\conninfo

You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

List existing DB

The list of databases already existing on the server can be obtained by the command

Pay attention to the Encoding, Collate, and Ctype columns. Knowledge of encodings may be useful to you in the future.

Create DB

Create heihei_ru_db DB encoded as utf8

CREATE DATABASE "heihei_ru_db" WITH OWNER "postgres" ENCODING 'UTF8';

CREATE DATABASE

Let's create a heihei database with utf8 encoding and specify values for Collate, Ctype and Template

CREATE DATABASE "heihei" WITH OWNER "postgres" ENCODING
'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C' TEMPLATE = template0;

CREATE DATABASE

To check the result

If you run the following command in bash

locale -a

Then you will get four available encodings at once

C
C.UTF-8
en_US.utf8
POSIX

But it won't be possible to create en_US.utf8 DB

CREATE DATABASE "heihei" WITH OWNER "postgres" ENCODING 'UTF8' LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8' TEMPLATE = template0;

It results in error

ERROR: invalid locale name: "en_US.UTF-8"

If you know how to solve this problem, please unsubscribe in the comments to the article.

UPD: After running same command in Ubuntu DB was successfully created

CREATE DATABASE "new_db" WITH OWNER "postgres" ENCODING 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' TEMPLATE = template0;

CREATE DATABASE

Drop DB

To remove DB use the DROP command

DROP DATABASE db_name;

DROP DATABASE

Check encodings

To check server encoding run

SHOW SERVER_ENCODING;

server_encoding ----------------- UTF8 (1 row)

For client encoding execute

SHOW CLIENT_ENCODING;

client_encoding ----------------- WIN1252 (1 row)

When you are in interactive PostgreSQL mode, the prefix appears in the console

db=>

Where db is the name of the current database

Show the address of the current directory

[andrei@localhost ~]$

You can return to PostgreSQL by doing

exit

db=>

Enter the DB

To start working with a database, you need to know its name, for example let's say you need a database named simply HeiHei_ru_DB

Use the command

\c HeiHei_ru_DB

You are now connected to database "HeiHei_ru_DB" as user "postgres".

If you work in Linux and the database is on the same host you can run

psql -h 127.0.0.1 -d DATABASENAME -U DATABASEUSERNAME

To view the list of tables, enter

\dt

To view the entire table person you can already use the standard

SELECT * FROM person;

Run script from file

First, let's check that everything is fine with the environment variables.

To do this, we introduce to the console psql.exe press Enter and check that bash does not complain about an unknown command.

If he complains, read my tips in the article PATH system variable

Write a script script.sql

CREATE TABLE person ( id int, first_name VARCHAR(50), last_name VARCHAR(50), gender VARCHAR(5), date_of_birth DATE )

Apply this script to the database HeiHei_ru_DB

I have postgres running locally on port 5433. You may have on 5432 - check.

cat script.sql | psql.exe -h localhost -p5433 -U postgres HeiHei_ru_DB

Password for user postgres:
CREATE TABLE

Let' try something closer to a real script

You need to introduce some restrictions on the fields of the table and add properties to them.

CREATE TABLE booking_sites ( id BIGSERIAL NOT NULL PRIMARY KEY, company_name VARCHAR(50) NOT NULL, origin_country VARCHAR(50) NOT NULL, age VARCHAR(3) NOT NULL, date_of_birth DATE NOT NULL, website_url VARCHAR(50) );

Теперь запустим этот скрпит уже не в тестовую а в рабочую базу данных heihei (которая совпадает с названием сайта HeiHei.ru, но если написать .ru будет синтаксическая ошибка ERROR: syntax error at or near ".")

cat booking_sites.sql | psql.exe -h localhost -p5433 -U postgres heihei

Password for user postgres:
CREATE TABLE

DB Content

In the previous paragraph, we created the booking_sites table in the heihei database

Let's make sure that the script worked successfully

Let's go to the hei hei database and check if the table has been created

\c heihei

You are now connected to database "heihei" as user "postgres".

heihei=# \dt

Table Description

If the table was created some time ago. You might have already forgotten which specific columns it contains.

To describe the table, use the command \d

\d booking_sites

Users

Get list of users

postgres=# \du

List of roles Role name | Attributes | Member of -----------+------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication | {}

Change Table

When you need to update the name of a table column, use the ALTER command

Suppose the website_url field is missing 50 characters. Let's increase the length to 60.

ALTER TABLE booking_sites ALTER column website_url TYPE VARCHAR(60);

Let's check if the table has changed

\d booking_sites

Import from .csv

To open the file, it is advisable to put it in a folder

C:\Users\Public\ - для Windows
/tmp - for Linux and BSD

Or correctly configure permissions - otherwise it will be mistake

Example of an import script

set datestyle to "US"; COPY public.people (id, date, name, address, comment) FROM '/tmp/file.csv' DELIMITER ',' CSV HEADER ENCODING 'UTF8' QUOTE '"' ESCAPE '''';

To exit console

Get current schema

SELECT current_schema();

current_schema ---------------- public (1 row)

PostgreSQL script from Bash

To execute Bash script with PostgreSQL commands it is enough to create script.sh file.

#!/bin/bash PGPASSWORD=PASSWD psql -h HOST -U USERNAME -d DB_NAME -с "YOUR_COMMAND"

Example

#!/bin/bash PGPASSWORD=QWERTY psql -h 127.0.0.1 -U andrei -d urnsu -с "SELECT * FROM news"

And execute it by running

. script.sh

If the SQL script is large and contains complex syntax, it is not so easy to make friends with the bash syntax.

In such a situation, it may be useful to create a separate file sql_script.sql and the bash script file bash_script.sh

From bash_script.sh you can call sql_script.sql as follows

#!/bin/bash cat sql_script.sql | PGPASSWORD=QWERTY psql -h 127.0.0.1 -U andrei -d urnsu

. bash_script.sh

Environmental Variables

$PGDATA - data directory address

echo $PGDATA

/var/lib/pgsql/data

Related Articles
	PostgreSQL
	Установка в CentOS
	Установка в Ubuntu
	postgresql.conf: Конфигурационный файл
	SELECT
	WHERE
	Репликация master slave
	Write Ahead Log
	recovery.conf
	Help
	Ошибки