#StackBounty: #postgresql #postgresql-9.6 Setup VIP for PostgreSQL Replication

Bounty: 50

I have been assigned a task to setup Streaming replication in Postgresql. I am not a DBA by profession, doing this thing for the first time. I have set up replication, now as we don’t want to change the database connection string in the application during failover, I need to set up VIP for the same so that I provide only one IP in the connection string.

I have used this article for setting and testing VIP. I am able to setup VIP on a machine and able to connect PostgreSQL with this VIP. But when I tried to assign the same VIP to the slave server, i got an error that cannot assign requested address bind.

I need help to setup VIP and make it work with streaming replication so that applications always point to the master server i.e. point to new master after failover.


Get this bounty!!!

#StackBounty: #load-balancing #postgresql #scaling horizontal scaling and load-balancing for PostgreSQL with ProxySQL

Bounty: 50

I have a PostgreSQL DB running in Google Cloud SQL that I need to scale horizontally. Cloud SQL makes it easy to add read-only replicas, but does nothing to help with load-balancing traffic to the replicas.

I’m looking for something that will help me with load-balancing between the replicas. My app is running in Kubernetes (on GKE in the same cloud project as the database) so if the solution can be deployed to Kebernetes that would be great.

There’s lots of good tutorials for doing this using ProxySQL but they all assume you are using MySQL, and the ProxySQL config file seems to have MySQL specific fields, but I haven’t found anything that explicitly says “you can’t use ProxySQL with PostgreSQL” or “ProxySQL does work with PostgreSQL, and here’s how to do it…”

So my question is can I use ProxySQL with PostgreSQL? If so, what would the config file look like? If not what equivalent can I use instead?


Get this bounty!!!

#StackBounty: #postgresql #database-design Posgres database: how to model multiple attributes that can have also multiple value, and ha…

Bounty: 50

I have three entities, Items, Categories and Attributes.

An Item can be in one or multiple Categories, so there is M2M relation.

Item               ItemCategories                   Categories     

id name            item_id category_id             id    name     
1  alfa               1         1                   1   chipset
                      1         2                   2   interface 

An Item can have multiple Attributes depending on the ‘Categories’ they are in.

For example the items in Category ‘chipset’ can have as attributes: ‘interface’, ‘memory’ ‘tech’.

This attributes have a set of predefined value that don’t change often, but they can change.

For example: ‘memory’ can only be ddr2, ddr2, ddr4.

Attributes                                 CategoryAttributes

id name     values                          category_id attribute_id    
1  memory   {ddr2, ddr3, ddr4}              1              1

An Item that is in the ‘chipset’ Category has access to the Attribute and can only have Null or the predefined value of the attribute.

I thinking to use Enum or Json for Atribute values, but I have tow other conditions:

ItemAttributes

item_id attribute_id   value
 1        1          {ddr2, ddr4}

1) If an Attribute appears in 2 Categories, and an Ithe is in both categories, only once an attribute can be show.

2) I need to use the value with rank , so if two corresponding attribute values appear for an item, the rank should be greater if is only one, or the value don’t exist.

3)Creating separate tables for Attributes is not an option, because the number is not fixed, and can be big.

So, I don’t know exactly the best options in the database design are to constrain the values, and use for order ranking.


Get this bounty!!!

#StackBounty: #postgresql #docker #ssl #tcp #traefik How to connect to Traefik TCP Services with TLS configuration enabled?

Bounty: 50

I am trying to configure Traefik so that I would have access to services via domain names, and that I would not have to set different ports. For example, two MongoDB services, both on the default port, but in different domains, example.localhost and example2.localhost. Only this example works. I mean, other cases probably work, but I can’t connect to them, and I don’t understand what the problem is. This is probably not even a problem with Traefik.

I have prepared a repository with an example that works. You just need to generate your own certificate with mkcert. The page at example.localhost returns the 403 Forbidden error but you should not worry about it, because the purpose of this configuration is to show that SSL is working (padlock, green status). So don’t focus on 403.

Only the SSL connection to the mongo service works. I tested it with the Robo 3T program. After selecting the SSL connection, providing the host on example.localhost and selecting the certificate for a self-signed (or own) connection works. And that’s the only thing that works that way. Connections to redis (Redis Desktop Manager) and to pgsql (PhpStorm, DBeaver, DbVisualizer) do not work, regardless of whether I provide certificates or not. I do not forward SSL to services, I only connect to Traefik. I spent long hours on it. I searched the internet. I haven’t found the answer yet. Has anyone solved this?

PS. I work on Linux Mint, so my configuration should work in this environment without any problem. I would ask for solutions for Linux.


If you do not want to browse the repository, I attach the most important files:

docker-compose.yml

version: "3.7"

services:
    traefik:
        image: traefik:v2.0
        ports:
            - 80:80
            - 443:443
            - 8080:8080
            - 6379:6379
            - 5432:5432
            - 27017:27017
        volumes:
            - /var/run/docker.sock:/var/run/docker.sock:ro
            - ./config.toml:/etc/traefik/traefik.config.toml:ro
            - ./certs:/etc/certs:ro
        command:
            - --api.insecure
            - --accesslog
            - --log.level=INFO
            - --entrypoints.http.address=:80
            - --entrypoints.https.address=:443
            - --entrypoints.traefik.address=:8080
            - --entrypoints.mongo.address=:27017
            - --entrypoints.postgres.address=:5432
            - --entrypoints.redis.address=:6379
            - --providers.file.filename=/etc/traefik/traefik.config.toml
            - --providers.docker
            - --providers.docker.exposedByDefault=false
            - --providers.docker.useBindPortIP=false

    apache:
        image: php:7.2-apache
        labels:
            - traefik.enable=true
            - traefik.http.routers.http-dev.entrypoints=http
            - traefik.http.routers.http-dev.rule=Host(`example.localhost`)
            - traefik.http.routers.https-dev.entrypoints=https
            - traefik.http.routers.https-dev.rule=Host(`example.localhost`)
            - traefik.http.routers.https-dev.tls=true
            - traefik.http.services.dev.loadbalancer.server.port=80
    pgsql:
        image: postgres:10
        environment:
            POSTGRES_DB: postgres
            POSTGRES_USER: postgres
            POSTGRES_PASSWORD: password
        labels:
            - traefik.enable=true
            - traefik.tcp.routers.pgsql.rule=HostSNI(`example.localhost`)
            - traefik.tcp.routers.pgsql.tls=true
            - traefik.tcp.routers.pgsql.service=pgsql
            - traefik.tcp.routers.pgsql.entrypoints=postgres
            - traefik.tcp.services.pgsql.loadbalancer.server.port=5432
    mongo:
        image: mongo:3
        labels:
            - traefik.enable=true
            - traefik.tcp.routers.mongo.rule=HostSNI(`example.localhost`)
            - traefik.tcp.routers.mongo.tls=true
            - traefik.tcp.routers.mongo.service=mongo
            - traefik.tcp.routers.mongo.entrypoints=mongo
            - traefik.tcp.services.mongo.loadbalancer.server.port=27017
    redis:
        image: redis:3
        labels:
            - traefik.enable=true
            - traefik.tcp.routers.redis.rule=HostSNI(`example.localhost`)
            - traefik.tcp.routers.redis.tls=true
            - traefik.tcp.routers.redis.service=redis
            - traefik.tcp.routers.redis.entrypoints=redis
            - traefik.tcp.services.redis.loadbalancer.server.port=6379

config.toml

[tls]
[[tls.certificates]]
certFile = "/etc/certs/example.localhost.pem"
keyFile = "/etc/certs/example.localhost-key.pem"

Build & Run

mkcert example.localhost # in ./certs/
docker-compose up -d

Step by step

  1. Install mkcert
  2. Clone my code
  3. In certs folder run mkcert example.localhost
  4. Start container by docker-compose up -d
  5. Open page https://example.localhost/ and check if it is secure connection
  6. If address http://example.localhost/ is not reachable, add 127.0.0.1 example.localhost to /etc/hosts
  7. Download and run Robo 3T
  8. Create new connection:
    • Address: example.localhost
    • Use SSL protocol
    • Self-signed Certificate
  9. Test tool (image below)
  10. Try connect that way to postgres or redis (other clients of course)

test


Get this bounty!!!

#StackBounty: #ssl #postgresql #sqldeveloper SSL certificate error when connecting to PostgreSQL database with Oracle SQL Developer on …

Bounty: 50

The error I'm gettingI have a postgresql 9.3 database running in a RHEL 6.2 kvm virtual machine. It’s part of the application I support for work, and I cannot make any configuration changes to the contents of the VM itself. In production it would be deployed to bare metal, this vm is just for my own testing needs. On my Windows work computer, I can successfully connect to the database on a similar vm using SQL Developer.

I have confirmed that I can successfully connect to the database from the vm host via psql on command line. selinux has been disabled temporarily on the vm for testing purposes.

After installing SQL Developer, openjdk, openjfx, and passing the path of my openjdk installation as requested by SQL Developer, loading the postgresql JDBC driver, and attempting to connect to the database, I get this error:

Status : Failure -SSL error: java.security.cert.CertificateException: Certificates do not conform to algorithm constraints

I’m kind of at a loss as to how to get around this. I’m not a java developer and I don’t really know how to correct this. After much Googling for similar errors the the best solution I could come up with was to add the following to /opt/sqldeveloper/sqldeveloper/bin/sqldeveloper.conf, which did not fix the problem or change the error:

AddVMOption -Djdk.certpath.disabledAlgorithms=MD2

I suspect this is still the right direction, but I don’t know what should go in place of MD2, or if this is really even the correct solution.

How do I get around this?


Get this bounty!!!

#StackBounty: #postgresql #apache-spark #pyspark #apache-spark-sql #bigdata Writing more than 50 millions from Pyspark df to PostgresSQ…

Bounty: 300

What would be the most efficient way to insert millions of records say 50-million from a Spark dataframe to Postgres Tables.
I have done this from spark to
MSSQL in the past by making use of bulk copy and batch size option which was successful too.

Is there something similar that can be here for Postgres?

Adding the code I have tried and the time it took to run the process:

def inserter():
    start = timer()
    sql_res.write.format("jdbc").option("numPartitions","5").option("batchsize","200000")
    .option("url", "jdbc:postgresql://xyz.com:5435/abc_db") 
    .option("dbtable", "public.full_load").option("user", "root").option("password", "password").save()
    end = timer()
    print(timedelta(seconds=end-start))
inserter()

So I did the above approach for 10 million records and had 5 parallel connections as specified in numPartitions and also tried batch size of 200k.

The total time it took for the process was 0:14:05.760926 (fourteen minutes and five seconds).

Is there any other efficient approach which would reduce the time?

What would be the efficient or optimal batch size I can use ? Will increasing my batch size do the job quicker ? Or opening multiple connections i.e > 5 help me make the process quicker ?

On an average 14 mins for 10 million records is not bad, but looking for people out there who would have done this before to help answer this question.


Get this bounty!!!

#StackBounty: #postgresql #replication Postgresql 11 logical replication – stuck in `catchup` state

Bounty: 100

I’m running two postgresql 11 servers – master and slave (setup with logical replication).

The problem I’m facing is that today after weeks of uninterrupted work slave got out of sync with this error message:

2019-09-16 07:39:44.332 CEST [30117] ERROR:  could not send data to WAL stream: server closed the connection unexpectedly
                This probably means the server terminated abnormally
                before or while processing the request.
2019-09-16 07:39:44.539 CEST [12932] LOG:  logical replication apply worker for subscription "logical_from_master" has started
2019-09-16 07:39:44.542 CEST [27972] LOG:  background worker "logical replication worker" (PID 30117) exited with exit code 1

I did see this error message before and my process was to increase wal_sender_timeout on master (more details on this here: logical replication in postgresql – "server closed the connection unexpectedly")

So then I wanted to restore replication however the state of replication is stuck on catchup:

master=# select * from pg_stat_replication;
  pid  | usesysid | usename | application_name  |  client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |  state  |   sent_lsn   |  write_lsn   |  flush_lsn   |  replay_lsn  |    write_lag    |    flush_lag    |   replay_lag    | sync_priority | sync_state
-------+----------+---------+-------------------+---------------+-----------------+-------------+-------------------------------+--------------+---------+--------------+--------------+--------------+--------------+-----------------+-----------------+-----------------+---------------+------------
 86864 |    16680 | my_user    | logical_from_master | 10.10.10.10 |                 |       46110 | 2019-09-16 12:45:56.491325+02 |              | catchup | D55/FA04D4B8 | D55/F9E74158 | D55/F9E44CD8 | D55/F9E74030 | 00:00:03.603104 | 00:00:03.603104 | 00:00:03.603104 |             0 | async
(1 row)

I tried to restart slave a few times, with different combinations of subscription enabled and disabled – nothing helps, the replication status keeps staying on catchup. I can see sent_lsn and write_lsn values are changing so something is being sent through…

I’m not sure if this is relevant but on slave I tried following to see wal receiver status:

slave=# select * from pg_stat_wal_receiver ;
 pid | status | receive_start_lsn | receive_start_tli | received_lsn | received_tli | last_msg_send_time | last_msg_receipt_time | latest_end_lsn | latest_end_time | slot_name | sender_host | sender_port | conninfo
-----+--------+-------------------+-------------------+--------------+--------------+--------------------+-----------------------+----------------+-----------------+-----------+-------------+-------------+----------
(0 rows)

However when I do simple ps -aux | grep postgres:

postgres 26087  0.0  1.1 348788 46160 ?        S    12:45   0:00 /usr/lib/postgresql/11/bin/postgres -D /var/lib/postgresql/11/slave_logical -c config_file=/etc/postgresql/11/slave_logical/postgresql.conf
postgres 26089  0.0  0.2 349108 12080 ?        Ss   12:45   0:00 postgres: 11/slave_logical: checkpointer
postgres 26090  0.0  0.0 348996  3988 ?        Ss   12:45   0:00 postgres: 11/slave_logical: background writer
postgres 26091  0.0  0.1 348788  7204 ?        Ss   12:45   0:00 postgres: 11/slave_logical: walwriter
postgres 26092  0.0  0.1 349740  4396 ?        Ss   12:45   0:00 postgres: 11/slave_logical: autovacuum launcher
postgres 26093  0.0  0.0 170024  3028 ?        Ss   12:45   0:00 postgres: 11/slave_logical: stats collector
postgres 26094  0.0  0.1 349572  4516 ?        Ss   12:45   0:00 postgres: 11/slave_logical: logical replication launcher
postgres 26095  0.0  0.2 350164 10036 ?        Ss   12:45   0:00 postgres: 11/slave_logical: my_user db ::1(56086) idle
postgres 26125  4.5  0.9 359876 36884 ?        Ds   12:45   0:20 postgres: 11/slave_logical: logical replication worker for subscription 37614

So either logical replication does not produce anything within pg_stat_wal_receiver or for some reason worker details do not make it to the sys view. In the same the process seems to be there (or at least I am guessing after reading output of ps -aux above)

This is my slave configuration:

wal_level=logical
max_replication_slots=2
max_logical_replication_workers=4

wal_receiver_timeout=1200000

And this is my master:

wal_level=logical

max_replication_slots=10
max_wal_senders=10

# maximum wait time in milliseconds that the walsender process on the active master
# waits for a status message from the walreceiver process on the standby master.
wal_sender_timeout=1200000

I have no idea what to do (even worst, at this stage I have no idea what to check next…)

Can you help me understand what should I do to make my slave to catch up so it’s back to streaming state?


Edit (12 hours later)

When I checked in the morning synchronisation was still in catchup state

master=# select * from pg_stat_replication;
  pid  | usesysid | usename | application_name  |  client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |  state  |   sent_lsn   |  write_lsn   |  flush_lsn   |  replay_lsn  | write_lag | flush_lag | replay_lag | sync_priority | sync_state
-------+----------+---------+-------------------+---------------+-----------------+-------------+-------------------------------+--------------+---------+--------------+--------------+--------------+--------------+-----------+-----------+------------+---------------+------------
 12965 |    16680 | my_user    | logical_from_master | 10.10.10.10 |                 |       46630 | 2019-09-17 06:40:18.801262+02 |              | catchup | D56/248E13A0 | D56/247E3908 | D56/247E3908 | D56/247E3908 |           |           |            |             0 | async
(1 row)

But when I checked again 60 seconds later the results set was empty…

Logs now show multiple incarnations of the same error:

2019-09-16 22:43:33.841 CEST [20260] ERROR:  could not receive data from WAL stream: server closed the connection unexpectedly
                This probably means the server terminated abnormally
                before or while processing the request.
2019-09-16 22:43:33.959 CEST [26087] LOG:  background worker "logical replication worker" (PID 20260) exited with exit code 1
2019-09-16 22:43:34.112 CEST [3510] LOG:  logical replication apply worker for subscription "logical_from_master" has started
2019-09-16 23:12:01.919 CEST [3510] ERROR:  could not send data to WAL stream: server closed the connection unexpectedly
                This probably means the server terminated abnormally
                before or while processing the request.
2019-09-16 23:12:02.073 CEST [26087] LOG:  background worker "logical replication worker" (PID 3510) exited with exit code 1
2019-09-16 23:12:02.229 CEST [4467] LOG:  logical replication apply worker for subscription "logical_from_master" has started
2019-09-17 00:27:01.990 CEST [4467] ERROR:  could not send data to WAL stream: server closed the connection unexpectedly
                This probably means the server terminated abnormally
                before or while processing the request.
2019-09-17 00:27:02.131 CEST [26087] LOG:  background worker "logical replication worker" (PID 4467) exited with exit code 1
2019-09-17 00:27:02.177 CEST [6917] LOG:  logical replication apply worker for subscription "logical_from_master" has started
2019-09-17 01:05:35.121 CEST [6917] ERROR:  could not receive data from WAL stream: server closed the connection unexpectedly
                This probably means the server terminated abnormally
                before or while processing the request.
2019-09-17 01:05:35.220 CEST [26087] LOG:  background worker "logical replication worker" (PID 6917) exited with exit code 1
2019-09-17 01:05:35.252 CEST [8204] LOG:  logical replication apply worker for subscription "logical_from_master" has started
2019-09-17 01:49:08.388 CEST [8204] ERROR:  could not send data to WAL stream: server closed the connection unexpectedly
                This probably means the server terminated abnormally
                before or while processing the request.
2019-09-17 01:49:08.520 CEST [26087] LOG:  background worker "logical replication worker" (PID 8204) exited with exit code 1
2019-09-17 01:49:08.583 CEST [9549] LOG:  logical replication apply worker for subscription "logical_from_master" has started
2019-09-17 03:06:19.601 CEST [9549] ERROR:  could not receive data from WAL stream: server closed the connection unexpectedly
                This probably means the server terminated abnormally
                before or while processing the request.
2019-09-17 03:06:19.732 CEST [26087] LOG:  background worker "logical replication worker" (PID 9549) exited with exit code 1
2019-09-17 03:06:19.754 CEST [12120] LOG:  logical replication apply worker for subscription "logical_from_master" has started
2019-09-17 03:58:48.184 CEST [12120] ERROR:  could not receive data from WAL stream: server closed the connection unexpectedly
                This probably means the server terminated abnormally
                before or while processing the request.
2019-09-17 03:58:48.254 CEST [13781] LOG:  logical replication apply worker for subscription "logical_from_master" has started
2019-09-17 03:58:48.318 CEST [26087] LOG:  background worker "logical replication worker" (PID 12120) exited with exit code 1
2019-09-17 04:27:12.838 CEST [13781] ERROR:  could not receive data from WAL stream: server closed the connection unexpectedly
                This probably means the server terminated abnormally
                before or while processing the request.
2019-09-17 04:27:12.931 CEST [26087] LOG:  background worker "logical replication worker" (PID 13781) exited with exit code 1
2019-09-17 04:27:12.967 CEST [14736] LOG:  logical replication apply worker for subscription "logical_from_master" has started
2019-09-17 04:55:48.923 CEST [14736] ERROR:  could not receive data from WAL stream: server closed the connection unexpectedly
                This probably means the server terminated abnormally
                before or while processing the request.
2019-09-17 04:55:49.032 CEST [15686] LOG:  logical replication apply worker for subscription "logical_from_master" has started
2019-09-17 04:55:49.043 CEST [26087] LOG:  background worker "logical replication worker" (PID 14736) exited with exit code 1
2019-09-17 05:41:48.526 CEST [15686] ERROR:  could not receive data from WAL stream: server closed the connection unexpectedly
                This probably means the server terminated abnormally
                before or while processing the request.
2019-09-17 05:41:48.590 CEST [17164] LOG:  logical replication apply worker for subscription "logical_from_master" has started
2019-09-17 05:41:48.638 CEST [26087] LOG:  background worker "logical replication worker" (PID 15686) exited with exit code 1
2019-09-17 06:03:32.584 CEST [17164] ERROR:  could not receive data from WAL stream: server closed the connection unexpectedly
                This probably means the server terminated abnormally
                before or while processing the request.
2019-09-17 06:03:32.642 CEST [17849] LOG:  logical replication apply worker for subscription "logical_from_master" has started
2019-09-17 06:03:32.670 CEST [26087] LOG:  background worker "logical replication worker" (PID 17164) exited with exit code 1
2019-09-17 06:40:18.732 CEST [17849] ERROR:  could not receive data from WAL stream: server closed the connection unexpectedly
                This probably means the server terminated abnormally
                before or while processing the request.

In order to make replication to show up as catchup on master I now have to restart slave first…


Edit 2 – I thought maybe the rate at which sent_lsn is changing would help to establish how busy my servers are:

master=# select now(), * from pg_stat_replication ;
              now              |  pid   | usesysid | usename | application_name  |  client_addr  | client_hostname | client_port |        backend_start         | backend_xmin |  state  |   sent_lsn   |  write_lsn   |  flush_lsn   |  replay_lsn  | write_lag | flush_lag | replay_lag | sync_priority | sync_state
-------------------------------+--------+----------+---------+-------------------+---------------+-----------------+-------------+------------------------------+--------------+---------+--------------+--------------+--------------+--------------+-----------+-----------+------------+---------------+------------
 2019-09-17 08:31:02.547143+02 | 100194 |    16680 | my_user    | logical_from_master | 10.10.10.10 |                 |       46766 | 2019-09-17 08:23:05.01474+02 |              | catchup | D56/24B1BC88 | D56/24A39B58 | D56/24A39B58 | D56/24A39B58 |           |           |            |             0 | async
(1 row)

master=# select now(), * from pg_stat_replication ;
             now              |  pid   | usesysid | usename | application_name  |  client_addr  | client_hostname | client_port |        backend_start         | backend_xmin |  state  |   sent_lsn   |  write_lsn   |  flush_lsn   |  replay_lsn  | write_lag | flush_lag | replay_lag | sync_priority | sync_state
------------------------------+--------+----------+---------+-------------------+---------------+-----------------+-------------+------------------------------+--------------+---------+--------------+--------------+--------------+--------------+-----------+-----------+------------+---------------+------------
 2019-09-17 08:34:02.45418+02 | 100194 |    16680 | my_user    | logical_from_master | 10.10.10.10 |                 |       46766 | 2019-09-17 08:23:05.01474+02 |              | catchup | D56/24B54958 | D56/24A39B58 | D56/24A39B58 | D56/24A39B58 |           |           |            |             0 | async
(1 row)

master=# select now(), * from pg_stat_replication ;
              now              |  pid   | usesysid | usename | application_name  |  client_addr  | client_hostname | client_port |        backend_start         | backend_xmin |  state  |   sent_lsn   |  write_lsn   |  flush_lsn   |  replay_lsn  | write_lag | flush_lag | replay_lag | sync_priority | sync_state
-------------------------------+--------+----------+---------+-------------------+---------------+-----------------+-------------+------------------------------+--------------+---------+--------------+--------------+--------------+--------------+-----------+-----------+------------+---------------+------------
 2019-09-17 08:41:01.815997+02 | 100194 |    16680 | my_user    | logical_from_master | 10.10.10.10 |                 |       46766 | 2019-09-17 08:23:05.01474+02 |              | catchup | D56/24B778B0 | D56/24A39B58 | D56/24A39B58 | D56/24A39B58 |           |           |            |             0 | async
(1 row)

When I look at above I’m worried that write_lsn does not follow sent_lsn – I don’t know if this is correct.

Checkpoints are happening every 15 minutes and I can’t see much IO through iostat.

When I executed select * from pg_stat_replication for the fourth time nothing was showing up (suggesting replication died by itself). Coincidentally checkpoint was completing around that time (I restarted replication on slave and waited until next checkpoint completion time and this time worker have not died).

There is one worrying sign I noticed in logs while looking for checkpoints:

2019-09-17 08:49:02.155 CEST,,,35656,,5d382555.8b48,10873,,2019-07-24 11:31:01 CEST,,0,LOG,00000,"checkpoint starting: time",,,,,,,,,""
2019-09-17 08:56:32.086 CEST,,,35656,,5d382555.8b48,10874,,2019-07-24 11:31:01 CEST,,0,LOG,00000,"checkpoint complete: wrote 5671 buffers (0.1%); 0 WAL file(s) added, 0 removed, 1 recycled; write=449.927 s, sync=0.000 s, total=449.930 s; sync files=138, longest=0.000 s, average=0.000 s; distance=19690 kB, estimate=398335 kB",,,,,,,,,""
2019-09-17 09:04:02.186 CEST,,,35656,,5d382555.8b48,10875,,2019-07-24 11:31:01 CEST,,0,LOG,00000,"checkpoint starting: time",,,,,,,,,""
2019-09-17 09:04:51.376 CEST,,,35656,,5d382555.8b48,10876,,2019-07-24 11:31:01 CEST,,0,LOG,00000,"checkpoint complete: wrote 490 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=49.187 s, sync=0.000 s, total=49.190 s; sync files=41, longest=0.000 s, average=0.000 s; distance=3165 kB, estimate=358818 kB",,,,,,,,,""

It seems that session_start_time column refers date from 24th of July. Coincidentally this is when I started replication on master…


Get this bounty!!!

#StackBounty: #postgresql #amazon-web-services #amazon-rds Postgres roles and users – permission denied for table

Bounty: 50

I configured Postgres 11.2 database running on RDS following the instructions in https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/

  1. I logged in as the master user created during RDS creation
  2. Executed CREATE SCHEMA myschema;
  3. Executed script from the link above
-- Revoke privileges from 'public' role
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;

-- Read-only role
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE mydatabase TO readonly;
GRANT USAGE ON SCHEMA myschema TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly;

-- Read/write role
CREATE ROLE readwrite;
GRANT CONNECT ON DATABASE mydatabase TO readwrite;
GRANT USAGE, CREATE ON SCHEMA myschema TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO readwrite;

-- Users creation
CREATE USER reporting_user1 WITH PASSWORD 'some_secret_passwd';
CREATE USER reporting_user2 WITH PASSWORD 'some_secret_passwd';
CREATE USER app_user1 WITH PASSWORD 'some_secret_passwd';
CREATE USER app_user2 WITH PASSWORD 'some_secret_passwd';

-- Grant privileges to users
GRANT readonly TO reporting_user1;
GRANT readonly TO reporting_user2;
GRANT readwrite TO app_user1;
GRANT readwrite TO app_user2;

After that I connected in as app_user1 and created a new table and added one row to it. Then, I connected using reporting_user1 and tried to SELECT * FROM that new table but saw following message on the console:

ERROR:  permission denied for table first_table
SQL state: 42501

What am I missing in my configuration? I expect the reporting_user1 to have read access to all tables created by the app_user1 in myschema.


Get this bounty!!!

#StackBounty: #python #django #python-3.x #postgresql How do I write a Django query that finds words in a Postgres column?

Bounty: 50

I’m using Django and Python 3.7. How do I scan for words in a Django query? A word is a string surrounded by whitespace (or the beginning or end of a line). I have this …

def get_articles_with_words_in_titles(self, long_words):
    qset = Article.objects.filter(reduce(operator.or_, (Q(title__icontains=x) for x in long_words)))
    result = set(list(qset))

but if “long_words” contains things like [“about”, “still”], it will match Articles whose titles have things like “whereabouts” or “stillborn”. Any idea how to modify my query to incorporate word boundaries?


Get this bounty!!!

#StackBounty: #ruby-on-rails #json #postgresql #jsonb How to update different json (jsonb) attributes asynchronously with rails and pos…

Bounty: 50

I have a large json object stored in a postgres table column, an example of the schema is like this:

create_table “document” do |t|
    t.jsonb “data”, default: []
end

At the moment I’m updating json in the column like so:

# find document in rails then…
doucment.data[‘some_attribute’][2][‘another_attribute’] = 100
doucment.save

However I write this json attribute many times and sometimes data becomes lost because if two calls are writing it at the same time, then the whole object will be saved or over written with the current objects old data.

For example if there’s two different saves going through at the same with the following

Save 1:

doucment.data[‘some_attribute’][2][‘another_attribute’] = 100
doucment.save

Save 2:

doucment.data[‘some_attribute’][2][‘different_attribute’] = 200
doucment.save

then either one of the attributes data will be lost because the other one will save it’s json but with old data that hasn’t been refreshed yet.

What is the best way to go about making both calls save the new data correctly.

Is there any json method that can just go in and update one attribute, like update_attribute but for a jsonb attribute?


Get this bounty!!!