0

I'm stumped. 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 configure the external IP address and test that we can connect to port 5432 from the 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'll be back Psql: The server closed the 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 test its selected changes.
So this should allow connections from any source.

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

Any suggestions or ideas?

• 1 answer

Sorting:

Activity

1

I'd like to try a few things to get more information:

Postgres tail log into the server to see what's going on there when connected.

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.

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

You can also troubleshoot by shutting down the Postgres server and using nc to listen on 5432 . Then 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, I tried to connect from AWS. Turns out it was the tier I was using in the load balancer to only allow access to IP-2 IP ranges that AWS publishes. When I disabled irule I was able to connect immediately. So this must be surreal logic since I checked the public IP of my EC2 instance and it was indeed listed in the irule via CIDR notification. -

PostgreSQL is an open-source, cross-platform object-relational DBMS. In this article you will learn how to install PostgreSQL on Ubuntu Linux, connect to it and run a couple of simple SQL queries, as well as how to set up backups.

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. For now, 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 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;

You can view a list of existing schemes using 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.

System Administration

This post is a brief instruction for beginners, for those who installed PostgreSQL for the first time. Here's all the information you need 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.

1. 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:

1. $ sudo -u postgres psql postgres postgres

To be able to connect via the network, you need to add the 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 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 ordinary users to view environment variables using ps. But if you want, you need to write in the terminal:

1. 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:

1. 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.

1. SELECT * FROM pg_user ;

Creating a new PostgreSQL user

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

1. CREATE USER username WITH password "password" ;

Or you can use the terminal.

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

You will be prompted to enter a password.

Changing the user password

1. ALTER USER username WITH PASSWORD "password" ;

Changing user roles

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

1. ALTER ROLE username WITH CREATEDB ;

Database management

Displaying a list of databases in the psql terminal: The same from the 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 up 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, 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 the pg_hba.conf configuration file, it must also have the appropriate connection permissions.

1. GRANT CONNECT ON DATABASE dbname TO dbadmin ;

Attention : If you are working on Windows OS and have not yet installed PostgreSQL and Debit Plus V12, then you can download from the download page the Debit Plus V12 assembly 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 the Debit Plus V12 software package, after which you can configure the connection and import the PostgreSQL database, following these instructions.

To connect a PostgreSQL database, you must perform the following steps:

Launch “Debit Plus V12” and add a new base (context menu “Add new”).

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

"DBMS" - POSTGRE.

"Database server" - localhost.

“Database name” - specify the name of the database from the PostgreSQL DBMS in lowercase Latin letters (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 indicate the correspondence of the “tags” to the physical databases. This may be necessary, for example, if the databases of several enterprises will refer to the same directories (counterparties, items, etc.). This case is not discussed further.

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

Save your changes by clicking the “Done” button.

Run pgAdmin (PostgreSQL DBMS 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 out the remaining 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 database archive dpbase.bakup (drive\DebetPlusV12\base\pgdpbase).

You can add the user dpadmin, which is the administrator in Debit Plus V12 by default (without a 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 databases (menu “Refresh”).

Note : If you do not want to assign “Superuser” rights to the user, then assign rights to “Tables” and “Views”. To do this, select the corresponding object and select the “Grant Wizard” context menu item.

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

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

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 checkboxes as default (there must be checkmarks in the “Reindex” column for all database tables).

After completing the modification, you can start working.

Launch “Debit Plus V12” and add a new base (context menu “Add new”).

Leave a checkmark in the “Add existing to list” field:

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 database server port, default is 5432.

“Database name” - enter the name of the database in lowercase Latin letters.

"Database schema name" - dpbase.

Check only the “Use Debit Plus authorization” box.

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

Save your 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 start working.