Launching postgresql from the command line. Basic PostgreSQL commands. Setting up data types

Hello everyone, today I want to make a short reminder about the main PostgreSQL commands. You can work with PosgreSQL both interactively and from command line. The program is psql. I am sure that this list will be very useful to you and will save you time searching through different resources. Let me remind you that this is an open-source project, based on the Postgres DBMS, released in 1986, it is being developed by a worldwide group of PGDG developers, essentially 5-8 people, but despite this, it is developing very intensively, introducing new functions and fixing old bugs and errors.

Basic PostgreSQL commands in interactive mode:

  • \connect db_name – connect to the database named db_name
  • \du – list of users
  • \dp (or \z) – list of tables, views, sequences, access rights to them
  • \di – indices
  • \ds – sequences
  • \dt – list of tables
  • \dt+ - list of all tables with descriptions
  • \dt *s* - list of all tables containing s in the name
  • \dv – representations
  • \dS – system tables
  • \d+ – table description
  • \o – send query results to a file
  • \l – list of databases
  • \i – read incoming data from a file
  • \e – opens the current contents of the request buffer in the editor (unless otherwise specified in the environment of the EDITOR variable, vi will be used by default)
  • \d “table_name” – description of the table
  • \i running a command from an external file, for example \i /my/directory/my.sql
  • \pset – command to configure formatting options
  • \echo – displays a message
  • \set – Sets the value of an environment variable. Without parameters, displays a list of current variables (\unset – deletes).
  • \? – psql reference
  • \help – SQL reference
  • \q (or Ctrl+D) – exit the program

Working with PostgreSQL from the command line:

  • -c (or –command) – run an SQL command without going into interactive mode
  • -f file.sql - execute commands from file file.sql
  • -l (or –list) – displays a list of available databases
  • -U (or –username) – specify the user name (for example postgres)
  • -W (or –password) – password prompt
  • -d dbname - connect to the dbname database
  • -h – host name (server)
  • -s – step-by-step mode, that is, you will need to confirm all commands
  • –S – single-line mode, that is, switching to new line will execute the query (gets rid of; at the end of the SQL construct)
  • -V – version of PostgreSQL without entering interactive mode

Examples:

psql -U postgres -d dbname -c “CREATE TABLE my(some_id serial PRIMARY KEY, some_text text);” - execution of a command in the dbname database.

psql -d dbname -H -c “SELECT * FROM my” -o my.html - output the query result to an html file.

PosgreSQL utilities (programs):

  • createdb and dropdb – creating and dropping a database (respectively)
  • createuser and dropuser – creation and user (respectively)
  • pg_ctl – a program designed to solve general control tasks (starting, stopping, setting parameters, etc.)
  • postmaster – multi-user PostgreSQL server module (configuring debug levels, ports, data directories)
  • initdb – creating new PostgreSQL clusters
  • initlocation – a program for creating directories for secondary database storage
  • vacuumdb – physical and analytical database support
  • pg_dump – archiving and restoring data
  • pg_dumpall – backup entire PostgreSQL cluster
  • pg_restore – database restoration from archives (.tar, .tar.gz)

Examples of creating backups:

Creating a backup of the mydb database, in compressed form

Pg_dump -h localhost -p 5440 -U someuser -F c -b -v -f mydb.backup mydb

Creating a backup of the mydb database, in the form of a regular text file, including the command to create the database

Pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb

Creating a backup of the mydb database, in compressed form, with tables containing payments in the name

Pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *payments* -f payment_tables.backup mydb

Dump data from just one, specific table. If multiple tables need to be backed up, the table names are listed using the -t switch for each table.

Pg_dump -a -t table_name -f file_name database_name

Creation backup copy with compression in gz

Pg_dump -h localhost -O -F p -c -U postgres mydb | gzip -c > mydb.gz

List of most commonly used options:

  • -h host - host, if not specified then localhost or the value from the PGHOST environment variable is used.
  • -p port - port; if not specified, 5432 or the value from the PGPORT environment variable is used.
  • -u - user, if not specified, the current user is used, the value can also be specified in the PGUSER environment variable.
  • -a, -data-only - dump only data; by default, data and schema are saved.
  • -b - include large objects (blogs) in the dump.
  • -s, -schema-only - dump only the schema.
  • -C, -create - adds a command to create a database.
  • -c - adds commands to delete (drop) objects (tables, views, etc.).
  • -O - ​​do not add commands to set the owner of an object (tables, views, etc.).
  • -F, -format (c|t|p) - dump output format, custom, tar, or plain text.
  • -t, -table=TABLE - specify a specific table for the dump.
  • -v, -verbose - output detailed information.
  • -D, -attribute-inserts - dump using the INSERT command with a list of property names.

Backup all databases using the pg_dumpall command.

Pg_dumpall > all.sql

Restoring tables from backups:

psql - restoring backups that are stored in a regular text file(plain text);
pg_restore - restoring compressed backups (tar);

Restoring an entire backup while ignoring errors

Psql -h localhost -U someuser -d dbname -f mydb.sql

Restoring an entire backup, stopping at the first error

Psql -h localhost -U someuser -set ON_ERROR_STOP=on -f mydb.sql

To restore from a tar archive, we first need to create a database using CREATE DATABASE mydb; (if the -C option was not specified when creating the backup) and restore

Pg_restore -dbname=mydb -jobs=4 -verbose mydb.backup

Restoring a gz-compressed database backup

psql -U postgres -d mydb -f mydb

I think the postgresql database will now be more understandable to you. I hope this list of PostgreSQL commands was useful to you.

Before anyone can access the database, you must start the database server. The server program is called postgres. This program must know where to find the data it needs to work. To do this, use the -D option. So the easiest way to start the server is:

$ postgres -D /usr/local/pgsql/data

In this case the server will be started as active process(not in the background). This must be done from a PostgreSQL user account. Without the -D option, the server will try to look up the data in the directory from the PGDATA environment variable. If this variable is not present, then the server will not start.

It is usually much more convenient to run the server in the background. To do this, use normal Unix shell syntax:

$ postgres -D /usr/local/pgsql/data >logfile 2>&1 &

It is very important to store the server output and error output somewhere, as shown above. This will help in diagnosing problems. (See section 23.3 for information on processing log files.)

The postgres program also accepts command line options. This is discussed in more detail in the postgres man page and in Chapter 18.

However, these shell commands can get boring quickly. Therefore, there is a wrapper program pg_ctl, which allows you to do the same thing, but much simpler. For example

pg_ctl start -l logfile

Typically you need to start the database server when you boot your computer. Autorun scripts depend on the system. You can find some options in the contrib/start-scripts directory. Installing them may require superuser rights.

Different systems have different conditions for starting the daemon at boot. Many systems have a /etc/rc.local or /etc/rc.d/rc.local file. Other systems use the init.d or rc.d directories. However, the server must be run as the PostgreSQL user, and not on behalf superuser or another user. So perhaps you should use this form of the command su -c "..." postgres. For example:

su -c "pg_ctl start -D /usr/local/pgsql/data -l serverlog" postgres

Here are some assumptions for different OS (In any case, be sure that you specify the correct installation folder and username):

  • FreeBSD look at the file /contrib/start-scripts/freebsd in the source folder
  • OpenBSD add the following lines to /etc/rc.local
if [ -x /usr/local/pgsql/bin/pg_ctl -a -x /usr/local/pgsql/bin/postgres ]; then su - -c "/usr/local/pgsql/bin/pg_ctl start -l /var/postgresql/log -s" postgres echo -n "postgresql" fi
  • Linux add
    • /usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data
  • NetBSD use either the FreeBSD or Linux approach, depending on preference
  • Solaris create a file called /etc/init.d/postgresql with the following line
    • su - postgres -c "/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data"

    While the server is running, its PID is stored in postmaster.pid in the data directory. This is used to ensure that multiple server instances are not running in the same data directory. It can also be used to stop the server.

    17.3.1 Server startup errors

    There are several common errors due to which the server may not start. Check the server log for error messages or run it manually (without redirecting standard output or error output). Below we explain some of the most common error messages:

    LOG: could not bind IPv4 socket: Address already in use

    HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.

    FATAL: could not create TCP/IP listen socket

    This usually means exactly what it says: You are trying to start another server on the same port that another server is already running on. However, if the port is not in use, then the reason may be different. For example, trying to start a server on a reserved port will also lead to a similar error.

    $ postgres -p 666

    LOG: could not bind IPv4 socket: Permission denied

    HINT: Is another postmaster already running on port 666? If not, wait a few seconds and retry.

    FATAL: could not create TCP/IP listen socket

    Message like

    FATAL: could not create shared memory segment: Invalid argument

    DETAIL: Failed system call was shmget (key=5440001, size=4011376640, 03600).

    usually means that the kernel shared memory limit is less than what the PostgreSQL workspace is trying to create (4011376640 bytes in our example). Or it may mean that your kernel is not configured to support System-V style shared memory. As a "crutch" you can try to start the server with fewer buffers (). Eventually you will want to reconfigure the kernel to increase the amount of shared memory allowed. Additionally, you may see this message when you try to run multiple server instances on the same machine if their total memory requirements exceed kernel limits.

    Error like

    FATAL: could not create semaphores: No space left on device

    DETAIL: Failed system call was semget(5440126, 17, 03600).

    Not means that you have run out of disk space. This means that the kernel limit on the number of System V semaphores is less than how many PostgreSQL wants to create. As in the previous case, you can use the "crutch" and start the server with a reduced number of allowed connections (max_connections), but in the end you will still just reconfigure your kernel.

    If you receive the error " illegal system call", then most likely shared memory and semaphores are not supported by your kernel at all. In this case, your only option is to reconfigure the kernel to enable support for these features.

    17.3.2 Problems with client connection

    Although client-side connection errors have different causes and depend on the specific application, however, some of them are directly related to whether the server is running. Errors other than those listed below must be resolved with the specific application.

    psql: could not connect to server: Connection refused

    Is the server running on host "server.joe.com" and accepting

    TCP/IP connections on port 5432?

    This is the standard "I can't find the server I'm supposed to talk to" error. It's similar to the error above about TCP/IP. Most likely they forgot to configure the server to accept TCP/IP connections.

    Additionally, you may see this error when trying to connect to local server via unix sockets:

    psql: could not connect to server: No such file or directory

    Is the server running locally and accepting

    connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

    By the last line you can check that the client is trying to connect to the right place. If there really is no server there, then the error message from the kernel will be either Connection refused or No such file or directory as in our example. (It's important to note that Connection refused in this case does not mean that the server received your connection request and rejected it. This situation will result in another error message, as shown in Section 19.4). Other error messages like Connection timed out may indicate more serious problems such as network delays.

    In this article I will show you the 15 most useful commands for managing postgreSQL.

    1. How to change root password in PostgreSQL?

    $ /usr/local/pgsql/bin/psql postgres postgres Password: (oldpassword) # ALTER USER postgres WITH PASSWORD 'tmppassword'; $ /usr/local/pgsql/bin/psql postgres postgres Password: (tmppassword)

    Changing the password for a regular user occurs in the same way. The root user can change the password for any user.

    # ALTER USER username WITH PASSWORD 'tmppassword';

    2. How to install PostgreSQL into autostart?

    $ su - root # tar xvfz postgresql-8.3.7.tar.gz # cd postgresql-8.3.7 # cp contrib/start-scripts/linux /etc/rc.d/init.d/postgresql # chmod a+x / etc/rc.d/init.d/postgresql

    3. Check the server status

    $ /etc/init.d/postgresql status Password: pg_ctl: server is running (PID: 6171) /usr/local/pgsql/bin/postgres “-D” “/usr/local/pgsql/data” [ Comment: This message indicates that the server is running and is working normally] $ /etc/init.d/postgresql status Password: pg_ctl: no server running [ Comment: This message indicates that the server is not running]

    4. How to start, stop, restart PostgreSQL?

    # service postgresql stop Stopping PostgreSQL: server stopped ok # service postgresql start Starting PostgreSQL: ok # service postgresql restart Restarting PostgreSQL: server stopped ok

    5. How can I see what version of PostgreSQL is running?

    $ /usr/local/pgsql/bin/psql test Welcome to psql 8.3.7, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit test=# select version(); version —————————————————————————————————— PostgreSQL 8.3.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42) (1 row) test=#

    5. How to create a user in PostgreSQL?

    There are two methods for this..

    Method 1: We create a user using the PSQL shell using the CREATE USER command.

    # CREATE USER ramesh WITH password 'tmppassword'; CREATE ROLE

    Method2: We create a user using the createuser shell command.

    $ /usr/local/pgsql/bin/createuser sathiya Shall the new role be a superuser? (y/n) n Shall the new role be allowed to create databases? (y/n) n Shall the new role be allowed to create more new roles? (y/n) n CREATE ROLE

    6. How to create a database in PostgreSQL?

    There are 2 methods for this.

    Method1: We create a database through a PSQL shell using the CREATE DATABASE command.

    # CREATE DATABASE mydb WITH OWNER ramesh; CREATE DATABASE

    Method2: We use the createdb command.

    $ /usr/local/pgsql/bin/createdb mydb -O ramesh CREATE DATABASE

    7. Do we get a list of all databases in Postgresql?

    # \l List of databases Name | Owner | Encoding ———-+———-+———- backup | postgres | UTF8 mydb | ramesh | UTF8 postgres | postgres | UTF8 template0 | postgres | UTF8 template1 | postgres | UTF8

    8. How to delete a database in PostgreSQL?

    # \l List of databases Name | Owner | Encoding ———-+———-+———- backup | postgres | UTF8 mydb | ramesh | UTF8 postgres | postgres | UTF8 template0 | postgres | UTF8 template1 | postgres | UTF8# DROP DATABASE mydb; DROP DATABASE

    9. Use the built-in help for commands

    Team \? will display a help line for the PSQL command. \h CREATE will show help for all commands that start with CREATE.

    # \? # \h CREATE # \h CREATE INDEX

    10. How to get a list of all tables in a given database in Postgresql?

    # \d

    For an empty database, you will receive the message “No relations found.”

    11. How can I find out the request execution time?

    # \timing - after executing this command, each subsequent request will show the execution time.

    # \timing Timing is on. # SELECT * from pg_catalog.pg_attribute ; Time: 9.583 ms

    12. How to backup and restore databases and tables in PostgreSQL?

    This question is quite large and I will publish it later in a separate article.

    13. How to view the list of available functions in PostgreSQL?

    To get a list of available functions, say \df+

    # \df # \df+

    14. How to edit a PostgreSQL query in the editor?

    # \e

    \e will open an editor where you can edit the query and save it.

    15. Where can I find the postgreSQL history file?

    Similar to the ~/.bash_history file, postgreSQL stores all sql commands in the ~/.psql_history file.

    $ cat ~/.psql_history alter user postgres with password 'tmppassword'; \h alter user select version(); create user ramesh with password ‘tmppassword’; \timing select * from pg_catalog.pg_attribute;

    The minimal setup of PostgreSQL on Windows without the need to install a database is described. Launched via bat file. Can work from a flash drive or portable hard drive.

    Download binaries for Windows

    Go to the PosgreSQL website in the Download/Windows section and click on the link zip archive in the paragraph for Advanced users. Select the desired version and go ahead.

    We extract the files from the archive to the desired directory, for example:
    D:\psql

    Running PostgreSQL without installation

    Bat script to run on Windows without installation:


    @ECHO ON
    @REM Set environment variables for PostgreSQL
    @SET PATH=”%CD%\bin”;%PATH%
    @SET PGDATA=%CD%\data
    @SET PGDATABASE=postgres
    @SET PGUSER=postgres
    @SET PGPORT=5432
    @REM Create new DB with Russian encoding only at first run
    @rem %CD%\bin\initdb -U postgres -A trust -E UTF8 —locale=russian_russia
    @REM Create new DB with English encoding only at first run
    %CD%\bin\initdb -U postgres -A trust -E UTF8 —locale=american_usa
    @%CD%\bin\pg_ctl -D %CD%/data -l logfile start
    @ECHO Press Enter to stop server
    @pause
    @%CD%\bin\pg_ctl -D %CD%/data stop

    We put the script in the root of the directory where PostgreSQL is located, for example:
    D:\psql\pg.bat

    Updating PostgreSQL version

    If you need to update your PostgreSQL version, you need to take a few simple steps. Download archive from new version, extract it to a separate directory and initialize the database. Described above.

    Data transfer is described in detail. In short, you need to run the script from the bin directory of the latest version:

    cd D:\psql-new\bin
    pg_upgrade.exe --username=postgres
    --old-datadir "D:\pgsql-old\data"
    --new-datadir "D:\pgsql-new\data"
    --old-bindir "D:\pgsql-old\bin"
    --new-bindir "D:\pgsql-new\bin"

    Changing the locale of PostgreSQL

    If you don’t like the translated messages and the pgAdmin3 interface, then you need to set the desired database encoding when you first start it.

    However, in Windows installation encoding different from the system one is a mystery that will have to be solved. The system does not understand standard encoding names, and those that it does understand are not so easy to find.

    To set the encoding " en_US.UTF-8american_usa» command:
    initdb -U postgres -A trust -E UTF8 —locale=american_usa

    To set the encoding " ru_RU.UTF-8"it is necessary to initialize the database with the locale" russian_russia» command:
    initdb -U postgres -A trust -E UTF8 —locale=russian_russia

    For other encodings, you need to select combinations yourself using the following sources on MSDN:

    Useful commands

    Below are the SQL queries that need to be entered through the PostgreSQL command line. We launch it with the command:
    D:\pgsql\bin\psql —username=postgres

    Or via the pgAdmin3 GUI:
    D:\pgsql\bin\pgAdmin3.exe

    Version check:
    select version();

    Current processes:
    SELECT datname,pid,query FROM pg_stat_activity;

    Database creation:
    CREATE DATABASE test_database;

    Creating and changing a user:
    CREATE USER developer WITH password '123456';
    ALTER USER developer WITH password '654321';

    Make superuser:
    ALTER ROLE developer SUPERUSER;

    User rights:
    GRANT ALL privileges ON DATABASE test_database TO developer;

    Import from dump:
    gunzip -c test_database.sql.gz| psql test_database

    Python

    gunzip -c test_database.sql.gz| psql test_database-U user-h localhost

    gunzip-c< em >test_database. sql. gz< / em >| psql< em >test_database< / em >- U< em >user< / em >- h< em >localhost< / em >


    psql test_database test_user< dump_file.sql

    Creating a table:

    Python

    CREATE SEQUENCE post_ids; /* counter for index */ CREATE TABLE posts (id INTEGER PRIMARY KEY DEFAULT NEXTVAL("post_ids"), title CHAR(64), content TEXT);



    RELATED ARTICLES