PostgreSQL is a cross-platform object-relational DBMS with open source code. 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. 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;
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. : 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 software package
“Debit Plus V12”, after which you can set up 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 (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: Typically 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.
A vulnerability (CVE-2019-18634) has been identified in the sudo utility, which is used to organize the execution of commands on behalf of other users, which allows you to increase 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 (recording, converting and […]
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 […]
Release presented Linux distribution Mint 19.2, the second update to the Linux Mint 19.x branch, based on the Ubuntu 18.04 LTS package base and supported until 2023. The distribution is fully compatible [...]
New BIND service releases are available that contain bug fixes and feature improvements. 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, the release of ZFS on Linux 0.8.0 is presented, implementation 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 Internet protocols and architecture, has completed an RFC for the ACME (Automatic Certificate Management Environment) protocol […]
The non-profit certification authority Let’s Encrypt, which is controlled by the community and provides certificates free of charge to everyone, summed up the results of the past year and talked about plans for 2019. […]
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. Settings remote access to 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 the IP-2 ranges of the IPs 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. -
Connection
