What must be specified under host name / address if I want to access my PostgreSQL database from a home PC?

Suppose I did the following things.


  1. Operating system: Windows 10
  2. PostgreSQL: 12.0

Things done:

  1. Change listen_addresses listen_addresses = '*' in the postgresql.conf
  2. Add host all all md5 in the pg_hba.conf

I would now like to create a new server / database, which I usually specify localhosts as my host name / address.

But since I want to go live, what do I have to say? And how do I connect with the other PC?

Enter image description here

postgresql – can I be helped? my question should = something like .000534 but I get 0

can be helped my query should = something like .000534 but I get 0.
Could you have a suggestion to help me?
Thank you very much

psql -Upostgres -dcans -c "select (choose COUNT () FROM brd.gaf_info where can_piecesre_sucbattery = & # 39; pr & # 39; and
gaf_start_date> & # 39; 2019-11-13 01: 01: 01.754 & # 39; /
(Select COUNT (
) FROM brd.gaf_info where gaf_start_date> & # 39; 2019-11-13 01: 01: 01.754 & # 39;); "

postgresql – psql restore failed invalid command N

I have initiated a table dump and wherever the NULL value comes, has the dump file N for this.

During recovery, I get this error.
invalid command N

The database encoding is UTF8,

I took the dump and tried to restore on the same server but different database. There is a workaround for this. But I'm still trying to fix the problem without making any further changes to the database encoding.

Table structure:

CREATE TABLE person.person (
    businessentityid integer NOT NULL,
    persontype character(2) NOT NULL,
    namestyle public."NameStyle" DEFAULT false NOT NULL,
    title character varying(8),
    firstname public."Name" NOT NULL,
    middlename public."Name",
    lastname public."Name" NOT NULL,
    suffix character varying(10),
    emailpromotion integer DEFAULT 0 NOT NULL,
    additionalcontactinfo xml,
    demographics xml,
    rowguid uuid DEFAULT public.uuid_generate_v1() NOT NULL,
    modifieddate timestamp without time zone DEFAULT now() NOT NULL,
    CONSTRAINT "CK_Person_EmailPromotion" CHECK (((emailpromotion >= 0) AND (emailpromotion <= 2))),
    CONSTRAINT "CK_Person_PersonType" CHECK (((persontype IS NULL) OR (upper((persontype)::text) = ANY (ARRAY('SC'::text, 'VC'::text, 'IN'::text, 'EM'::text, 'SP'::text, 'GC'::text)))))

error line

COPY person.person (businessentityid, persontype, namestyle, title, firstname, middlename, lastname, suffix, emailpromotion, additionalcontactinfo, demographics, rowguid, modifieddate) FROM stdin;
1       EM      f       N      Ken     J       Sánchez N      0       N      0     92c4279f-1207-48a3-8448-4636514eb7e2    2009-01-07 00:00:00

Using letsencrypt with postgresql

I'm trying to use letsencrypt to generate certificates to encrypt communication between a remote client and my postgresql database server. I'm not sure how to do it. What type of certificates do I need for the customer? Which certificate files should I save on the server?
I have reviewed this documentation for reference, but it is still unclear which certificates go where.
Thank you in advance!

postgresql – Postgres with seq scan with filter for indexed column + EXISTS for associated table

I have the following query, which takes about 25-35 seconds to execute:

EXPLAIN (analyze, buffers, format text) SELECT booking.*
FROM booking
WHERE booking.reference_number = '9999999999' OR booking.booking_id = '9999999999' OR 
        EXISTS (
            SELECT * FROM booking_customer 
            WHERE booking_customer.booking_id = booking.booking_id AND 
                (booking_customer.email = '9999999999' OR booking_customer.phone = '9999999999') AND 
                booking_customer.deleted = false

The EXPLAIN issue can be found here: https://explain.depesz.com/s/RPNV

As you can see from the chart, the bookings table is searched one after another, even though filtered by indexed columns. reference_number and booking_id, The booking_customer However, the table uses the index scan correctly.

Does it have something to do with it? EXISTS or OR there clauses?

My table structure is as follows:

                              Table "public.booking"
        Column         |           Type           | Collation | Nullable | Default
 deleted               | boolean                  |           |          |
 booking_id            | character varying        |           | not null |
 reference_number      | character varying        |           |          |
 booking_owner         | character varying        |           |          |
 checkin_date          | timestamp with time zone |           |          |
 checkout_date         | timestamp with time zone |           |          |
 status                | character varying        |           |          |
 hold_till             | timestamp with time zone |           |          |
 version               | integer                  |           | not null |
 comments              | text                     |           |          |
 extra_information     | json                     |           |          |
 cancellation_reason   | character varying        |           |          |
 cancellation_datetime | timestamp with time zone |           |          |
 created_at            | timestamp with time zone |           | not null | now()
 modified_at           | timestamp with time zone |           | not null | now()
    "booking_pkey" PRIMARY KEY, btree (booking_id)
    "ix_booking_reference_number" UNIQUE, btree (reference_number)
    "idx_booking_checkin_date" btree (checkin_date)
    "idx_booking_checkout_date" btree (checkout_date)
    "ix_booking_status" btree (status)
    "trgm_booking_ref_num" gist (reference_number gist_trgm_ops)

And customer table:

                          Table "public.booking_customer"
        Column         |           Type           | Collation | Nullable | Default
 deleted               | boolean                  |           |          |
 customer_id           | character varying        |           | not null |
 booking_id            | character varying        |           | not null |
 first_name            | character varying        |           |          |
 last_name             | character varying        |           |          |
 gender                | character varying        |           |          |
 age                   | integer                  |           |          |
 nationality           | character varying        |           |          |
 phone                 | character varying        |           |          |
 email                 | character varying        |           |          |
 gst_addr_field1       | character varying        |           |          |
 gst_addr_field2       | character varying        |           |          |
 gst_addr_city         | character varying        |           |          |
 gst_addr_state        | character varying        |           |          |
 gst_addr_country      | character varying        |           |          |
 gst_pincode           | character varying        |           |          |
 legal_name            | character varying        |           |          |
 created_at            | timestamp with time zone |           | not null | now()
 modified_at           | timestamp with time zone |           | not null | now()
    "booking_customer_pkey" PRIMARY KEY, btree (customer_id, booking_id)
    "book_cust_idx" btree (booking_id, customer_id)
    "ix_booking_customer_email" btree (email)
    "ix_booking_customer_phone" btree (phone)
    "trgm_cust_last_name" gist (last_name gist_trgm_ops)

Details about the total records in the table are as follows:

db=> SELECT COUNT(*) FROM booking;
(1 row)

db=> SELECT COUNT(*) FROM booking_customer;
(1 row)

db=> SELECT COUNT(*) FROM booking WHERE reference_number = '9999999999' OR booking_id = '9999999999';
(1 row)

db=> SELECT COUNT(*) FROM booking_customer WHERE (email = '9999999999' OR phone = '9999999999') AND deleted = false;
(1 row)

db=> SELECT COUNT(DISTINCT(booking_id)) FROM booking_customer WHERE (email = '9999999999' OR phone = '9999999999') AND deleted = false;
(1 row)

So ideally I should have gotten 65196 Rows as a result, which may be a good reason to use sequence scan. However, the planner returns only 14 lines as an estimate. That's funny, considering that yesterday at midnight, I did vacuum analysis on both tables.

Even if I try with a different value for phone or email, giving about 1300 lines, the seq scan is still used for the booking table.

Is there a way to optimize this query?

Postgres details:

db=> SELECT version();
 PostgreSQL 9.6.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit
(1 row)

postgresql – what does this connection error message mean?

$ psql -h host db_name

psql: New process for connection could not be split: memory can not be allocated

I think I have enough free memory?

Other conditions when using jdbc:

Caused by: java.lang.IllegalStateException: Received resultset tuples, but no field structure for them
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2086)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:168)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:116)

org.postgresql.util.PSQLException: A connection could not be made using the requested protocol null.
        at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:59)
        at org.postgresql.jdbc.PgConnection.(PgConnection.java:216)
        at org.postgresql.Driver.makeConnection(Driver.java:404)
        at org.postgresql.Driver.connect(Driver.java:272)
        at java.sql.DriverManager.getConnection(DriverManager.java:664)
        at java.sql.DriverManager.getConnection(DriverManager.java:247)

  Detail: Failed on request of size 81160 in memory context "MessageContext".
java.lang.RuntimeException: org.postgresql.util.PSQLException: ERROR: out of memory
  Detail: Failed on request of size 81160 in memory context "MessageContext".

debian – kali linux v. 5.3.0 dpkg and postgresql are not updated

This is the error I keep getting when trying to update:

package configuration

┌────────────────────────────────────────────────────────────────────────────────────────────────── ┐
│ │
│ Outdated major version 11

Post PostgreSQL version 11 is outdated, but the server or client packages
│ are still installed. Please install the latest packages (postgresql-12
│ and postgresql-client-12) and update the existing clusters with
│ pg_upgradecluster (see man page).

│ Please note that the installation of postgresql-12 is done
│ Automatically create a default cluster 12 / main. If you want to upgrade
│ In the 11 / main cluster, you must remove the already existing 12 cluster
│ (pg_dropcluster –stop 12 main, details see man page).

│ The old server and client packages are no longer supported. After this
│ Existing clusters are updated, the postgresql-11 and
│ postgresql-client-11 packages should be removed.

│ For more information, see /usr/share/doc/postgresql-common/README.Debian.gz

I was trying to install, uninstall, update, pg_dropcluster, I'm not sure what else to do.

The attempt to find the time value has changed to postgresql

I have a table with an ID, a value and a date. There are many IDs, values, and dates in this table.

Records are regularly added to this table. The ID is unique, but occasionally the value changes.

How can I write a query that gives me the ID and time of the last value change? Note: The value may be reset from 1 to 2 and then back to 1.

Conditional Join query or another way to create a view based on two tables in Postgresql

I'm trying to create a view that combines these two materialized views with the following rules.

Returns only rows that exist in all_products, though they exist in visible_products, but if there is no result in visible_products, I want all the rows of all_products

I am unable to figure it out with links or union. Can this be solved with a case description? If both tables have rows, the inner join gives me the right result, and if visible_products do not have matching rows, a left join works, but I need a join or some other solution that works in both cases.

Both all_products and visible_products are materialized views where I can modify / add columns as needed.

Example a

id, market_id
1, 100
1, 101
1, 102

id, market_id
1, 100

result product_view

Example b

id, market_id
2, 103
2, 104
2, 105

id, market_id

result product_view
2, 103
2, 104
2, 105

Any help is greatly appreciated


postgresql – Calling PQconnectdbParams to connect to postgres reports a connection timeout error

What are the acceptable formats for sending connection string to PQconnectdbParams method to connect to postgres db?

I try to follow one, but it does not work and always displays timeout errors. I'm not sure what I did is wrong.

host=xxx.xxx.us-west-2.rds.amazonaws.com port=5432 dbname=dbname password=my_password user=user_name

The error message is as follows

ERROR: Unable to connect to postgresql server: timeout expired

If required, I can provide more details.

Note: I can connect to the DB psql Command from terminal but can not when I use PQconnectdbParams with the same credentials.