0

I'm at a dead end. We have a postgres 9.2 data base, so we can beat it for an hour. Now we need a mother to be able to connect to a new connection between our borders.

Again, we'll call the IP address and test that we can connect to port 5432 from the remote host via telnet. In such a rank, ce to bring, scho merezhne z'ednannya pratsyuє, firewalls and so on. All good.

If I try to connect for help:
psql -h db.host.com -d dbname -p 5432 -U

I'll turn around Psql: server shutting down z'ednannya znenatska.

I have reconsidered that
listen_addresses = "*" set in postgresql.conf

І on pg_hba.conf and maybe a line, as you read (only for testing)
Lord of all 0.0.0.0/0 md5

І I reloaded the data base in order to review the selected changes.
In this rank, tse can allow the death of any dzherel.

Set the psql version on the database server to 9.2.14, and on the client - 9.2.13.

Be it propositions or ideas?

• 1 confirmation

Sorting:

Activity

1

I want to try a few speeches to get more information:

hwist Postgres go to the server, to check what it says when connected.

Run psql --version , to change it, it will change the version of the server more or less. (I'm working, you've already robbed me, but I'll leave yoga here for naschakіv.)

Run strace psql .... to make sure you get as far as possible.

Tse, like before, sound like a problem for me. What is the telnet command, how do you use it? "Old IP" sounds like it's on AWS. Nalashtuvannya remote access VPC borrows a lot of money. What do you have for other services, for calls, how do you work?

You can also troubleshoot by turning on the Postgres server and using nc to listen for 5432 . Let's connect to telnet and see if you can push the data back and forth.

1

A pidlog, like a proposition. I zrobiv stretch and pereviryav versions. I have become less balanced by trying to connect to AWS. It appears that this is the tier, which I have won at the balancer of the balancer, to allow access only to the ranges of IP-2 ІС, like publishing AWS. If I wiggled irule, I instantly connected negatively. In this way, the logic is absolutely surreal, since I checked the public IP address of my EC2 instance, and it was effectively shown in the irule via CIDR notifications. -

System Administration

Tsey post - short instruction for beginners, for those who have previously installed PostgreSQL. Here is all the information you need to get started with PostgreSQL.

Connection to DBMS

The first thing you need to do is to get access to PostgreSQL, access like a supercorrier.
Authentication settings are found in the pg_hba.conf file.

1. local all postgres peer

It's worth talking about those that postgres can connect to any data base of a local PostgreSQL DBMS via a socket. If you do not need to enter a password, the operating system will transfer the name of the koristuvach, and it will be required for authentication.
Connecting:

1. $ sudo -u postgres psql postgres postgres

Sob the mother can be connected according to the measure, it is necessary to add to the row:

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. It's not too hard, as you often get greeted by the psql console. If you want to automate something like this, then the bad news is that psql doesn't accept a password as an argument. There are two ways to solve these problems: installing a password changer and saving a password for a special .pgpass file.

Installing a PGPASSWORD sharpening tool

I’ll tell you once again, what better way is not to win, for deyak Operating systems allow you to look over the most prominent coristuvacha change of sharpening for help ps. And if you want, you need to write in the terminal:

1. export PGPASSWORD=mypasswd

Change will be available at the current session. It is also necessary to set a change for all sessions, you need to add a row of butts to the .bashrc file or .bash_profile

Save the password for the .pgpass file

As we are talking about Linux, the file can be in $HOME (/home/username). The rights to write and read may be less with the Vlasnik (0600). The file needs to write rows of the form:

1. hostname:port:database:username:password

For the first few fields, you can write "*", which means the same day of filtering (the same choice).

Retrieval of background information

\? - see all available commands at once from their short description,
\h - show the list of available requests,
\h CREATE - see the result for a specific request.

DBMS management

How to view the list of PostgreSQL correspondents? Otherwise, you can query the table pg_user.

1. SELECT * FROM pg_user;

Creation of a new PostgreSQL koristuvach

From the psql shell, you can spawn with an additional CREATE command.

1. CREATE USER username WITH password "password" ;

Or you can speed up the terminal.

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

Entering a password will be requested.

Change the password of a koristuvach

1. ALTER USER username WITH PASSWORD "password";

Changing the roles of a koristuvach

Shchob koristuvach mav the right to create bazi danikh, vikonate ask:

1. ALTER ROLE username WITH CREATEDB;

Database management

Viewing the list of databases from the psql terminal: Same from the Linux terminal:

1. psql -l

Creating a database from psql (PostgreSQL Terminal)

1. CREATE DATABASE dbname OWNER dbadmin;

Creation of a new data base for an additional terminal:

1. createdb -O username dbname;

Setting up access rights to data base

Yakshcho koristuvach є vlasnik (owner) of the base of data, vin maє all rights. However, if you want to give access to someone else, you can use the help of the GRANT command. The request is lower to allow coristuvachevy to connect to the database of data. But don't forget about the configuration file pg_hba.conf, otherwise you should be allowed to connect.

1. GRANT CONNECT ON DATABASE dbname TO dbadmin;

PostgreSQL - cross-platform object-relational DBMS with vіdkritim exit code. For the purposes of this article, you will know how to install PostgreSQL in ubuntu linux, connect to the new one and get a couple of simple SQL queries, as well as about those, how to make a backup copy.

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 fix it from the DBMS through the shell:

sudo -u postgres psql

Let's create a test database of that test coristuvach:

CREATE DATABASE test_database;
CREATE USER test_user WITH password "qwerty" ;
GRANT ALL ON DATABASE test_database TO test_user;

To exit the shell, type \q .

Now let's try to fix it in the created database with the name 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" ) ,
loginCHAR(64),
passwordCHAR(64));

As a matter of fact, PostgreSQL doesn't have any control over auto_increment power. The postgres has natomist sequences. For now, it is enough to know that for the additional function nextval we can take unique numbers for a given sequence:

SELECT NEXTVAL("user_ids");

Having registered as the value behind the lock for the id field of the users table, the value NEXTVAL("user_ids" ), we achieved the same effect as auto_increment. When adding new records to the table, we may not specify an ID, so that a unique ID will be generated automatically. Dekіlka table can win one and the same sequence. In this way, we can guarantee that the values ​​of the fields in these tables do not change. This sense sequence has more bells, lower auto_increment.

Exactly the same table can be created and for the help of only one command:

CREATE TABLE users2 (
id SERIAL PRIMARY KEY ,
loginCHAR(64),
passwordCHAR(64));

In this case, the sequence of the id field is created automatically.

Now, for the help of the \d command, you can get a list of all available tables, and for the help of \d users, you can get a description of the users tables. If you didn't take away the information to tease you, try \d+ substitute \d . The list of databases can be taken with the \l command, and you can go to a specific database with the \c dbname command. To display the finalization of the commands, say \? .

It is important to note that in PostgreSQL, table and column names are capitalized to lower case. Even though the behavior is unbearable, you can scurried up with your paws:

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

Another feature of PostgreSQL, which can be blamed on the cob of work for the purpose of the DBMS, is the name of the "scheme". The schema allows for a wide range of names for tables, like a catalog with tables in the middle of the data base.

Scheme creation:

CREATE SCHEMA bookings;

Switching to the scheme:

SET search_path TO bookings;

You can look through the basic schemes with the \dn command. For promotions, a scheme is awarded for public names. In principle, you can successfully hack PostgreSQL without knowing about schemas. But with a robot with a degraded code, as well as with some borderline depressions, knowledge about schemes can be useful.

In another work with PostgreSQL, little is known about work with any other relational DBMS:

INSERT INTO users (login, password)
VALUES("afiskon", "123456");
SELECT * FROM users;

As soon as you try to connect to postgres from another machine, you will know if it fails:

psql -h 192.168.0.1 test_database test_user

psql: can't connect to server: Connection refused
Is the server running on host "192.168.0.1" and accepting
TCP/IP connections on port 5432?

To correct it, add a row:

listen_addresses = "localhost,192.168.0.1"

... the /etc/postgresql/9.2/main/postgresql.conf file as well.

Uvaga : If you are working on Windows and have not yet installed PostgreSQL and “Debit Plus V12”, then you can take advantage of the “Debit Plus V12” charge from a front-mounted PostgreSQL DBMS and a connected database (with a basic configuration for Ukraine). In another case, you will need to install the PostgreSQL DBMS and the Debit Plus V12 software package in advance, after which you will be able to fix and import the PostgreSQL database, following this instruction.

To connect the PostgreSQL database, you need to connect like this:

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

Fill in the sign in the field "Add to the list of real".

"DBMS" - POSTGRE.

"database server" - localhost.

"My data base" - enter the data base name from the PostgreSQL DBMS in lower case Latin characters (small characters).

Note: Sound vikoristovuєtsya one base. For special purposes, the database can be divided into sprats, then it is necessary to check the box next to “Vykoristovuvat kіlka data bases”, click the “Dalі” button and indicate the validity of “marks” to physical databases. It may be necessary, for example, in order to do so, as the bases of these large enterprises will be assisted by your own dovіdniki (counterparties, the nomenclature, too). This type of behavior is not discussed further.

"Catalog nalashtuvan" - specify the path to nalashtuvan base for PostgreSQL (disk \ DebetPlusV12 \ base \ pgdpbase).

Save your changes by pressing the Done button.

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

Enter the name of the server, localhost in the Host field, fill in the fields for the bugs.

Enter the name of the data base in the “Name” field (the same as it was entered in the “Server” field in the setup of the registration with the “Debit Plus V12” data base).

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

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

You can add a koristuvach dpadmin, who is the administrator of Debit Plus V12 for promotion (without a password).

Grant all the rights to this koristuvachev.

Note : You can leave out the dpadmin password, otherwise you will need to enter the postgres administrator name when activating the database.

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

Note : If you don't want to grant root Superuser rights, then grant rights to Tables and Views. For which, having seen the object, select the Grant Wizard context menu item.

On the Selection tab, click the Check all button, and on the Privileges tab, check the ALL icon and click the Add/Change button. Save change.

Activate the setup of data base registration at the Debit Plus V12 software (the context menu is "Active data").

The system will ask for a password to connect to the data base. Press "Yes".

Respect! Even if this prompt did not appear, start updating the database structures by selecting the menu item "Service" / "Updating database structures".

As a result, the next window will appear:

Leave all the badges for promotions (obviously the only badges in the "Reindex" column for all database tables).

After completing the modification, you can start the work.

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

Remove the checkmark from the field "Add to the list of real":

For the first field, enter a full name of the base (on a skin working computer, the name can be different).

"DBMS" - POSTGRE.

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

"Data base server port" - enter the data base server port for promo 5432.

"Im'ya bazi danih" - enter the im'ya bazi danikh in Latin characters in lower case.

"Im'ya base data schemes" - dpbase.

Put a tick next to the field "Wake authorization Debit Plus".

“Install directory” - specify the path to the instalation base for PostgreSQL (DebetPlusV12 base pgdpbase path).

Save the changes made by pushing the “Done” button, after which the active database is created.

Set startup options (head menu "Startup options")

When you see it, enter the password 150301 in the "Password to change password" field and click on the "Accept" button.

Enter the path to the JDebet folder on the server and press save. Build an active base and you can proceed to work.