postgresql – server closed the connection unexpectedly PSQL copy

I have this error in my PostgreSQL 9.6 in google cloud and really don’t know why. When trying to export large tables via copy. The table size is more or less 30GB and 16GB.

COPY data transfer failed: server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

I did quickly search in google and see these flag is important but I’m not sure if is true.

  • shared_buffers=>2492MB
  • temp_file_limit=>10268767kB.

And this is a configuration one Master and Salve

  • vCPU=>2
  • Memoria=>7,5 GB
  • SSD=>109 GB
  • PG 9.6

Any idea?

linux – psql: could not connect to server: no such file or directory exists, allowing communication via TCP / IP, ubuntu 18.04

I was installing postgres in Ubuntu 18.04

The command line I used for installation is:

sudo apt-get install postgresql postgresql-contrib

Since I entered this command line a few times, I got:

Reading package lists... Done
Building dependency tree       
Reading state information... Done
postgresql is already the newest version (10+190ubuntu0.1).
postgresql-contrib is already the newest version (10+190ubuntu0.1).
0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded.

as a result.

Things seem fine for now.

Then I tried to set the password for "postgres" using:

sudo -u postgres psql postgres

This is what I got:

psql: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

To address this problem I tried the following approaches:

  1. To find out which processes are running as user postgres, I entered:

    ps -u postgres -f

and this is what I got:

UID        PID  PPID  C STIME TTY          TIME CMD

I don't understand what they are and how important they are. It would be nice if you told me. That said, I didn't get much of a hint at this information.

  1. I am assuming I have not started postgresql yet due to this error:

    psql: could not connect to server: no such file or directory exists

so i tried:

sudo /etc/init.d/postgresql start

What I got:

( ok ) Starting postgresql (via systemctl): postgresql.service.

It seems I have something there. So I ran:

psql

But the same error appeared:

psql: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Right now, am I really confused if I have started my postgres? If so, why doesn't "psql" work? If not, what else does this error indicate?

Then I tried an alternative configuration approach:

https://help.ubuntu.com/community/PostgreSQL

check "Alternative server settings" from the link. The link per se is pretty good. However, the following command lines did not work for me:

sudo -u postgres createuser --superuser $USER

I have:

createuser: could not connect to database postgres: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

OK, let me connect to db postgres:

For your reference:
https://www.cyberciti.biz/faq/postgresql-remote-access-or-connection/

  1. Use the command line below to allow the remote IP address to access PostgreSQL:

    su – postgres

I have:

su: Authentication failure

2. Allow communication via TCP / IP. I think this part is quite important.

command line:

vi /var/lib/pgsql/data/pg_hba.conf

I inserted this line into the interface:
host all all 192.168.1.0 255.255.255.0 trust

I used ": wq" to save and exit, but this is what I got:

"/var/lib/pgsql/data/pg_hba.conf"
"/var/lib/pgsql/data/pg_hba.conf" E212: Can't open file for writing
Press ENTER or type command to continue

I had to close the terminal. I didn't know of other ways to quit smoking in this case.

It is the same here for:

$ vi /var/lib/pgsql/data/postgresql.conf

and

tcpip_socket = true

Just a little more information: (cl: command line, fb: comments)

  1. cl:

    ps aux | grep postgres

full board:

ubuntu    5599  0.0  0.0  21536  1004 pts/1    S+   23:15   0:00 grep --color=auto postgres
  1. cl:

    sudo apt install postgres

full board:

Reading package lists... Done
Building dependency tree       
Reading state information... Done
E: Unable to locate package postgres
  1. cl:

    sudo apt-get install postgres

full board:

Reading package lists... Done
Building dependency tree       
Reading state information... Done
E: Unable to locate package postgres

4)
When I use "sudo -i -u postgres" and type "psql"
I have nothing different.

If that is all.

Does allowing communication via TCP / IP make my "pqsl" work? If so, how do I save and exit?

View timestamps for long-running operations in the PSQL console

When I do long-running queries, I tend to use tmux and a psql console. It will not show me any result of when any operation started or completed.
How can I make the psql console display some timestamps?

linux: is there an equivalent of .ssh / config to use with psql?

In the .ssh/config file (at least that's the usual default location), I can define the hostname, username, port, etc. for ssh connections and store it with a user friendly name. That allows me to call ssh or scp without having to do all the tedious typing, but simply pressing, eg. ssh myconnection2.

Is there an equivalent for this functionality in psql? The man page mentions the .psqlrc file in terms of configuration, which is not exactly what I need, because it gets only after psql It is started.

The only decent halfway solution I have so far is to use aliasBut that comes with problems of its own in some cases. Any ideas if I'm missing something?

pgAdmin4: postgres password doesn't work, even after explicitly setting it via psql

Steps taken so far

  1. I connect to pgAdmin4 and click on the server "PostgreSQL 10".
  2. In the password request I try: "" (White), postgres, the master password that I was asked to configure at the first login, admin, 123456. All attempts fail.
  3. I quit pgAdmin4. In the terminal, I login to psql.
  4. I set the user password postgres to the password postgres
  5. Return to pgAdmin4. I enter the password set in step 4. The password fails again:

pgAdmin4 and psql

Can anyone advise me on how to login to PostgreSQL 10 server in pgAdmin4?

postgresql – PSQL: multiple rows vs. single row with JSON

What is the most optimal way to do this?

  • On the TV show page, I need to get all the progress of all the episodes of that TV show.
  • On the home page I need to get the "continue_watching"
  • In the episode, I need to get the progress of the episode.
  • Some television shows have more than 100 episodes.

Former:

USER_ID | SHOW_ID | LAST_SEEN_ID | LAST_SEEN_TIME
10      | 5       | 12           | ---

USER_ID | EPISODE_ID | PROGRESS
10      | 10         | 15
10      | 11         | 20
10      | 12         | 95

OR

USER_ID | SHOW_ID | PROGRESS                     | LAST_SEEN_ID | LAST_SEEN_TIME
10      | 5       | {"10":15, "11":20, "12": 95} | 12           | ---

At posgresql I can get individual progress with:

SELECT progress -> & # 39; 10 & # 39; as progress …

I think the best method is the first, but it will create many rows and this could make DB slower.

postgresql – psql ^ C takes a long time to cancel the query

I am using psql to query a large database. and (understandably) many queries I write take a long time. Normally I want to cancel these consultations and then I enter ^Cand psql responds with the message

Cancel request sent

This cancel request sometimes cancels queries quickly (milliseconds), but sometimes it takes a long time (> 10 minutes) to cancel the query and display a message where I can type new commands. These long cancellation times even occur in queries that have only been running for less than a second.

Why does psql take so long to cancel some queries? And what can I do to make psql cancel queries faster?


Version Information:

$ psql --version
psql (PostgreSQL) 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1)

psql: how do I generate a series from the last id in the table, generate_series (last id, 10000)

How do I generate a series from the last id in the table, generate_series (last id, 10000)? Let's say the last id is 101 in the table. I would like to generate identifiers 102-10000 in the table. I currently have this

    INSERT INTO table_a
    SELECT i, 'A' || i::text, 'UNKNOWN'
    FROM generate_series(102, 10000) AS t(i);

But if I don't know the value of the last id, how would I go about getting this?

Postgres script psql

I am trying to run a script through psql (terminal windows) but it fails.
I think it's something with confinement
insert image description here

insert image description here

postgresql – psql database is slow, shows long-running active queries, Statement_timeout does not work

I have a psql database that is accessed through a node.js API using a native mobile reaction application. It doesn't matter if the details of the code stack matter, but there are essentially queries in the psql db that remain as status = & # 39; active & # 39; for days that are slowing down the entire database. Using the following to verify:

SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

Manually kill active queries with

SELECT pg_cancel_backend(__pid__)

solve the problem and return the database to normal speed. But, of course, we want to make sure that this does not necessarily happen intermittently and slow down our application for all users.

Another thing to mention is that I tried to put a time limit on active queries with Statement_timeout, but I'm not sure I am doing this correctly. Trying it with …

SET statement_timeout = 1;
SELECT * from "table_name";

It does not work. That is … the previous SELECT statement takes 400 milliseconds and does not end. It seems I don't have this correct procedure. The statement works on

SET statement_timeout = 10000;
SELECT pg_sleep(15);

thereby canceling the second statement. Does that mean that Statement_timeout only works in inactive processes and not at runtime?

The details of the query (including anything out of the ordinary, everything else are normal statements of & # 39; where & # 39;)

  1. It is a select instruction that joins three tables: two of them are normal, one is a table of postal codes with more than 40k rows indexed in the postal code column.
  2. There is a CASE statement in the SELECT piece (before where), as such
(CASE
                        WHEN
                            (SELECT earth_distance(
                            ll_to_earth(z.lat, z.lng),
                            ll_to_earth((SELECT lat from zipcodes where zipcode = ''),
                                        (SELECT lng from zipcodes where zipcode = ''))) / 1609.34) <= 5
                        THEN 0
                        WHEN
                            (SELECT earth_distance(
                            ll_to_earth(z.lat, z.lng),
                            ll_to_earth((SELECT lat from zipcodes where zipcode = ''),
                                        (SELECT lng from zipcodes where zipcode = ''))) / 1609.34) <= 10
                        THEN 1
                        WHEN
                            (SELECT earth_distance(
                            ll_to_earth(z.lat, z.lng),
                            ll_to_earth((SELECT lat from zipcodes where zipcode = ''),
                                        (SELECT lng from zipcodes where zipcode = ''))) / 1609.34) <= 20
                        THEN 2
                        ELSE (SELECT earth_distance(
                            ll_to_earth(z.lat, z.lng),
                            ll_to_earth((SELECT lat from zipcodes where zipcode = ''),
                                        (SELECT lng from zipcodes where zipcode = ''))))
                    END   )
                    AS distance

Yes, it definitely seems that it would take some time, however, it does not for most users. And still, why would this query show up as & # 39; active & # 39; during days? For example, if you cannot find the zip code in the zip code table, you will not return anything or an error will occur.

Any help is really appreciated! Even if you see something out of place, don't hesitate to comment! Forgive me for anything simple that I missed :-)!