Postgresql connection to a remote database. Connecting a PostgreSQL database

PostgreSQL is a cross-platform open source object-relational database management system. source code. This article will show you how to install PostgreSQL in ubuntu linux, connect to it and execute a couple 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));

Note that unlike some other DBMSs, PostgreSQL does not have auto_increment columns. Instead, Postgres uses sequences. On the this moment it is 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 as auto_increment. When adding new records to the table, we may not specify an id, because a unique id will be generated automatically. Multiple tables may use the same sequence. Thus, we can guarantee that the values ​​of some fields in these tables do not intersect. In this sense, sequences are more flexible than auto_increment.

You can create exactly the same table with 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 generated automatically.

Now, using the \d command, you can see the list of all available tables, and using \d users, you can see the 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 is important to note that PostgreSQL defaults to lowercase table and column names. If this behavior is not desired, double quotes can be used:

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

Another feature of PostgreSQL that can be difficult to get started with this DBMS is the so-called "schemes". A schema is something like a namespace for tables, sort of like a directory of tables inside a database.

Schema creation:

CREATE SCHEMA bookings;

Switching to the scheme:

SET search_path TO bookings;

You can view the list of existing schemas with the \dn command. The default schema is public. In principle, you can successfully use PostgreSQL without knowing about the existence of schemas. But when working with legacy code, as well as in some edge cases, knowledge of 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 /etc/postgresql/9.2/main/postgresql.conf as well.

A vulnerability (CVE-2019-18634) has been identified in the sudo utility used to organize the execution of commands on behalf of other users, which allows you to elevate your privileges in the system. Problem […]

The release of WordPress 5.3 improves and expands the block editor introduced in WordPress 5.0 with a new block, more intuitive interaction, and improved accessibility. New features in the editor […]

After nine months of development, the FFmpeg 4.2 multimedia package is available, which includes a set of applications and a collection of libraries for operations on various multimedia formats (burning, converting, and […]

New features in Linux Mint 19.2 Cinnamon

Linux Mint 19.2 is a Long Term Support release that will be supported until 2023. It comes with updated software and contains improvements and many new […]

Linux Mint 19.2 distribution released

Release Presented Linux distribution Mint 19.2, the second update of the Linux Mint 19.x branch based on Ubuntu 18.04 LTS and supported until 2023. The distribution is fully compatible […]

New service releases of BIND are available that contain bug fixes and feature enhancements. New releases can be downloaded from the downloads page on the developer's website: […]

Exim is a message transfer agent (MTA) developed at the University of Cambridge for use in Unix systems connected to the Internet. It is freely available in accordance with […]

After almost two years of development, ZFS on Linux 0.8.0 is released, implementing file system ZFS packaged as a module for the Linux kernel. The module has been tested with Linux kernels from 2.6.32 to […]

The IETF (Internet Engineering Task Force), which develops the protocols and architecture of the Internet, has completed the formation of the RFC for the ACME (Automatic Certificate Management Environment) […]

Let’s Encrypt, a non-profit certification authority controlled by the community and providing certificates free of charge to everyone, summed up the past year and talked about plans for 2019. […]

0

I'm at a dead end. We have a postgres 9.2 database that we have been using for some time. Now we need to be able to connect to it from outside our network.

So we are setting up an external IP address and testing that we can connect to port 5432 from a remote host via telnet. So this proves that the network connection is working, firewalls, etc. are all good.

When I try to connect with:
psql -h db.host.com -d dbname -p 5432 -U user

i get back psql: server closed connection unexpectedly.

I checked that
listen_addresses = "*" is set in postgresql.conf

And in pg_hba.conf we have a line that reads (for testing purposes only)
host all all 0.0.0.0/0 md5

And I reloaded the database to check its selected changes.
So this should allow connections from any source.

The psql version on the DB server is 9.2.14 and on the client it is 9.2.13.

Any suggestions or ideas?

• 1 answer

Sorting:

Activity

1

I would like to try a few things to get more information:

Postgres tail log into the server to see what happens there when you connect.

Run psql --version to make sure it more or less matches the server version. (I see you've already done this, but I'll leave it here for posterity.)

Run strace psql .... to see how far it goes to failure.

It still sounds like a network issue to me. What is the telnet command you are running? "External IP" sounds just like you on AWS. Setting remote access to VPC takes many steps. Do you have any other services open to the outside that are open?

You can also troubleshoot by shutting down the Postgres server and using nc to listen on 5432 . Then connect to telnet and see if you can send data back and forth.

1

Paul, thanks for the suggestions. I made a stretch and checked the versions. And you were right, tried to connect from AWS. Turns out it was the tier I used in the load balancer to only allow access to the IP-2 IP ranges that AWS publishes. When I disabled irule I could connect immediately. So this must be surreal logic since I checked the public IP address of my EC2 instance and it was indeed listed in irule via the CIDR notification. -

System Administration

This post is a short tutorial for beginners, for those who have installed PostgreSQL for the first time. Here is all the information you need to get started with PostgreSQL.

Connecting to a DBMS

The first thing to do is access PostgreSQL, access as superuser.
The authentication settings are in the pg_hba.conf file.

1. local all postgres peer

This line says that the postgres user can connect to any database in the local PostgreSQL DBMS via a socket. You do not need to enter a password, the operating system will send the username, and it will be used for authentication.
Connecting:

1. $ sudo -u postgres psql postgres postgres

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

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

Authentication method md5 means that you will need to enter a password to connect. This is not very convenient if you frequently use the psql console. If you want to automate something, the bad news is that psql doesn't take 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 must say right away that it is better not to use this method, because some Operating Systems allow you to view ordinary users environment variables with ps. But if you want, you need to write in the terminal:

1. export PGPASSWORD=mypasswd

The variable will be available in 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 a .pgpass file

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

1. hostname:port:database:username:password

In the first four fields, you can write "*", which will mean no filtering (full selection).

Get Help

\? - will display all available commands along with their brief description,
\h - will give a list of all available requests,
\h CREATE - will give help on a specific request.

DBMS user management

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

1. SELECT * FROM pg_user ;

Creating a New PostgreSQL User

From the psql shell, this can be done with the CREATE command.

1. CREATE USER username WITH password "password" ;

Or you can use the terminal.

1. createuser -S -D -R -P username

The password will be requested.

Changing the user's password

1. ALTER USER username WITH PASSWORD "password" ;

Changing user roles

For the user to have the right to create databases, run the query:

1. ALTER ROLE username WITH CREATEDB ;

Database management

Listing databases in psql terminal: Same from Linux terminal:

1. psql -l

Creating a database from psql (PostgreSQL Terminal)

1. CREATE DATABASE dbname OWNER dbadmin ;

Creating a new database using the terminal:

1. createdb -O username dbname;

Setting database access rights

If the user is the owner of the database, then he has all rights. But if you want to give access to another user, then 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.

1. GRANT CONNECT ON DATABASE dbname TO dbadmin ;

Attention : If you work on Windows and have not yet installed PostgreSQL and Debit Plus V12, then you can download the Debit Plus V12 assembly from the download page with a pre-installed PostgreSQL DBMS and a connected database (with a basic configuration for Ukraine). Otherwise, you need to first install the PostgreSQL DBMS and software package"Debit Plus V12", after which it will be possible to set up a connection and import the PostgreSQL database, following this instruction.

To connect a PostgreSQL database, follow these steps:

Launch "Debit Plus V12" and add a new base ( context menu"Add New").

Leave a checkmark in the "Add to existing list" box.

"DBMS" - POSTGRE.

"Database Server" - localhost.

"Database name" - specify the name of the database from the PostgreSQL DBMS in Latin letters in lower case(in small letters).

Note: Usually one base is used. For special purposes, the database can be divided into several, then you need to check the box next to "Use multiple databases", click the "Next" button and specify the correspondence of "labels" to physical databases. This may be necessary, for example, if the databases of several enterprises will refer to the same directories (contractors, nomenclature, etc.). This case is not discussed further.

"Settings directory" - specify the path to the PostgreSQL database settings (drive\DebetPlusV12\base\pgdpbase).

Save changes made by clicking the "Finish" button.

Run pgAdmin (PostgreSQL database administrator), add a server (File/Add Server...) and add a new database (New Database... menu).

Enter the server name, localhost in the Host field, fill in the rest of the fields as desired.

Enter the name of the database in the "Name" field (the same as entered in the "Server" field in the settings for connecting to the "Debit Plus V12" database).

To import a database from an archive, use the "Restore..." menu

Specify the path to the dpbase.bakup database archive (drive\DebetPlusV12\base\pgdpbase).

You can add the user dpadmin, which is the default administrator in Debit Plus V12 (no password).

Assign all rights to this user.

Note : You can not add the dpadmin user, then when activating the database you will need to enter the postgres administrator name.

Refresh the list of bases ("Refresh" menu).

Note : If you don't want to give the user "Superuser" permissions, then give "Tables" and "Views" permissions. To do this, having selected the corresponding object, select the item of the context menu "Grant Wizard".

On the "Selection" tab, click the "Check all" button, and on the "Privileges" tab, check the "ALL" box and click the "Add/Change" button. Save your changes.

Activate the database connection setting in the Debit Plus V12 software (“Make active” context menu).

The system will ask for a password to connect to the database. Click Yes.

Attention! If such a message does not appear, then start updating the database structures by selecting the menu item "Tools" / "Updating database structures".

As a result, the following window will appear:

Leave all the default checkboxes (there must be checkmarks in the "Reindex" column for all database tables).

After the completion of the modification, you can start working.

Run "Debit Plus V12" and add a new base (context menu "Add new").

Leave a checkmark in the "Add to the list existing" box:

In the first field, enter an arbitrary name for the database (the name may be different on each working computer).

"DBMS" - POSTGRE.

"Database server" - the name or IP address of the server.

"Database server port" - specify the port of the database server, by default 5432.

"Database name" - specify the name of the database in Latin letters in lower case.

"Database schema name" is dpbase.

Check the box "Use Debit Plus Authorization" only.

"Settings directory" - specify the path to the PostgreSQL database settings (network path\DebetPlusV12\base\pgdpbase).

Save the changes by clicking the "Finish" button, and then make the database active.

Set launch options (main menu "Settings"/"Launch options")

In the window that appears, in the "Password for changing settings" field, enter the password 150301 and click on the "Accept" button.

Specify the path to the JDebet folder on the server and click save. Make the database active and you can get to work.