Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [postgresql-10]

PostgreSQL version 10

-1
votes
0answers
29 views

Regarding Installing postgresql ODBC driver in AIX 7.1 server

I want to install Postgresql ODBC driver in AIX 7.1 64 bit. I went to "https://www.postgresql.org/ftp/odbc/versions/" for rpm but I see directories like 1) src 2) old 3) msi 4) mm 5) dll I am ...
0
votes
0answers
14 views

PostgreSQL GIN pg_trgm default operator class - Does Not accept data Type uuid

I was trying to create GIN Indexes for an Array that stores uuid data type. CREATE INDEX IX_Gin_Column1 ON table_name USING gin (column1 gin_trgm_ops); PostgreSQL raised an error: ERROR: ...
0
votes
0answers
33 views

Rollup by unique combinations

I am attempting to do a ROLLUP or CUBE function, and I am wanting to get back unique combinations based on the values of a field, rather than particular columns. Here's what I have so far. With the ...
0
votes
1answer
21 views

Working memory on the connection string for postgres instance

I'm trying to understand how can I set the work_mem setting on the Postgres(v10) for a single connection/session. Currently I'm connection to my Postgres Instance in the cloud through a NodeJs app ...
0
votes
0answers
13 views

After upgrading from PG 9.3 to 10 indices became corrupted

So recently I was upgrading the database in production from Postgres 9.3 to 10. In addition to upgrade, we wanted to move the database to another machine. I know, doing it "in place" would be easier ...
0
votes
2answers
52 views

PostgreSQL 10 Optimizing slow query performance

I have the following query: select i.id as id, i.ts_updated as updated, i."name" as name, cast(attr.data_norm as text) as data_norm, cast(_59.json_agg as text) as _59, ...
2
votes
0answers
72 views

Restoring from PostgreSQL 9.5 dump to 10 is super slow

I am trying to restore database dump created on PostgreSQL 9.5.16 to PostgreSQL 10.7 and it is taking a lot of time to restore. Restoring the same dump to 9.5 doesn't take that much. So I am wondering ...
0
votes
0answers
6 views

Search path for DMS postgres 10 wrapper functions

I'm setting up DMS to perform replication from a non-RDS database. After running the suggested code to create wrapper functions, I'm finding our migrations are breaking with errors that indicate ...
0
votes
0answers
14 views

Is there a way to restore contents of pg_largeobject for BLOB data using pg_restore?

Product: PostgreSQL 10.3 We have a need to take a copy of our production database and restore that into a separate environment for testing and training purposes. The problem is that the database is ...
0
votes
0answers
14 views

Configure DB to respond only from a specific IP address

I have a server with multiple IP addresses (say ... 10.1.1.10 & 10.1.1.11) with several databases. And I want to say DBx is only responding if the client uses the IP 10.1.1.10 and DBy is only ...
1
vote
1answer
38 views

Recommended postgres parameters for index creation with 244gb RAM and 32 CPUs?

I have a 200 GB database with 26 tables that I need to create indexes on. Currently index creation takes a long time on the biggest tables, as they have ~12gb and 400,000,000 rows in them (>4 hours)....
0
votes
0answers
36 views

Designing a table with precalculated team values for fast read queries

My version of PostgreSQL is 10.7. As it's something I can't calculate on the fly due to the amount of times it might be used, I decided to store all the 11 million combinations and their result on a ...
1
vote
1answer
24 views

How can I check available space of an index's pages in PostgreSQL?

For tables, I can simply use pg_freespace to check available spaces like below: postgres=> SELECT count(*),avg(1000*avail/8192)/10 FROM pg_freespace('tablename'); count | ?column? ...
0
votes
0answers
25 views

Insert performance rapidly slowing down [duplicate]

I have a source table with about 500,000 not normalized data and insert it into 4 different tables related by foreign key constraints. The first couple of thousand rows are inserted at a speed of ...
1
vote
1answer
94 views

PostgreSQL: pg_rewind complains about wal_log_hints = on not been set, it is

I'm rather new to pg so please bear with me. I've set up two hosts and installed pg10 on both. Then I configured log streaming replication between them. This worked fine. I wanted to test fail over,...
2
votes
1answer
38 views

Logical replication - Manually triggering update of data on destination tables (after schema change)

I'm testing out Logical replication on Postgresql (10.4 at the moment) with a quite specific use case. I have some central DB (publisher) from where I need to replicate few tables to distributed DB's ...
0
votes
1answer
179 views

ERROR: could not connect to the publisher: FATAL: no pg_hba.conf entry for replication connection from host

I am trying to create a logical replication between 2 postgres servers. But when I try to create a subscription from the slave to the master I get the error ERROR: could not connect to the ...
0
votes
1answer
38 views

Safe to use the same temporary table name in parallel transactons?

Using postgreSQL 10 or 11, would it be safe creating temporary tables with the same name, each carrying different data? I do not intend to share the data between any sessions/connection/transactions. ...
0
votes
0answers
24 views

Is there a possibility to run 2 minor Postgres version in parallel (Postgres 10.x)?

I have Postgres db instance 10.6 running in production say node-b: rhel 7.6 while I am building another cluster (not instance) of 2 nodes. Out of these 2 nodes one posgres node will reside on this ...
0
votes
1answer
170 views

Postgres 10 and malformatted array literal when using EXECUTE

I am trying to create a function on Postgres 10 that would check whether all the given ids exist in the target table. CREATE OR REPLACE FUNCTION "public"."has_mismatching_ids"( _ids TEXT[], ...
2
votes
0answers
26 views

AWS RDS PostgreSQL: Significant query runtime difference between two (identical?) instances

TL;DR: Solution The problem was that one instance hat magnetic storage attached and was therefore significantly slower. Original Question I have two identical AWS RDS PostgreSQL 10.6 databases ...
0
votes
0answers
20 views

How to optimize a query with select, row number and a lot of LIKE

I try to find search rank of a product in today's product history records. For this I use 6 ILIKE and 2 inner join. I have indexed all columns but still so slow. It takes approximately 10 sec. to ...
0
votes
0answers
39 views

Why is restart_lsn not set, even on checkpoint - postgresql

A logical slot will emit each change just once in normal operation. The current position of each slot is persisted only at checkpoint, so in the case of a crash the slot may return to an earlier ...
0
votes
1answer
195 views

Error when creating a stored procedure in postgreSQL-10 [closed]

I couldn't get my first stored procedure to work, so I copied this example from the manual: CREATE PROCEDURE insert_data(a integer, b integer) LANGUAGE SQL AS $$ INSERT INTO tbl VALUES (a); INSERT ...
5
votes
2answers
124 views

Why are partial PostgreSQL HASH indices not smaller than full indices?

I want to create the most efficient index for a sparsely populated column. I only need equality operations, so a HASH index should be beneficial. Now I'm wondering why a partial HASH index isn't ...
0
votes
1answer
148 views

Preparing query in postgreSQL with variable table name?

I'm trying to do an INSERT INTO a table whose name is in the variable myTable. In MySQL, I'd do it like this: SET @SQL = CONCAT('INSERT INTO ',myschema'.'mytable,' SELECT action_name, user, ...'); ...
1
vote
2answers
61 views

Can a trigger create a TABLE when inserting data?

I have a trigger that keeps track of history of table dev by inserting a row in table dev_hist: CREATE OR REPLACE FUNCTION process_dev_hist() RETURNS TRIGGER AS $dev_hist$ DECLARE action_name ...
0
votes
0answers
25 views

How to create multiple tables based on existing tables in postgreSQL?

I have several tables, and I need to create a matching _hist table for each one. For example if I have: CREATE TABLE public.test ( first text, middle text, "last" text, ...
0
votes
1answer
39 views

In BEFORE UPDATE TRIGGER, how do I prevent changing nothing but timestamp?

I'm working on a trigger, and part of its functionality is to prevent UPDATE if the only field being updated is the timestamp field. My current solution is like this: CREATE OR REPLACE FUNCTION ...
1
vote
1answer
76 views

Can I change CITEXT columns to VARCHAR without encountering any surprise difficulties?

I went overboard on CITEXT columns on a particularly large table in my application. I would like to back some of these out as it's confusing how to trigger lookups on the desired indexes. My question ...
1
vote
1answer
77 views

Recommended procedure to remove old files after major version PostgreSQL database cluster upgrade

I (successfully) followed the upgrade procedure in §18.6.1 of the PostgreSQL Manual to upgrade a PostgreSQL database cluster from 10.7 to 11.2 (running Fedora 29). I would like to remove the old ...
4
votes
1answer
101 views

Slow left join lateral in subquery

I have three tables: units (apartment units), leases, and line_items (a better name would have been recurring rents). A unit has many leases and lease has many line items (associated with each rent ...
2
votes
1answer
25 views

Is it possible to add a spatial index to a partition with Postgres 10?

I am creating declarative partitions like: CREATE TABLE mydata.my_features ( id bigint, my_feature1 character(1), my_feature2 integer, ... my_geom geometry ) PARTITION BY ...
0
votes
2answers
451 views

pg_resetxlog command not found

I can't find pg_resetxlog under /usr/bin. postgres (PostgreSQL) 10.7 (Ubuntu 10.7-1.pgdg16.04+1) cd /usr/bin postgres@node-1:/usr/bin$ ./pg_resetxlog -su: ./pg_resetxlog: No such file or directory
2
votes
1answer
144 views

Postgres partial index on IS NULL not working

Postgres version Using PostgreSQL 10.3. Table definition CREATE TABLE tickets ( id bigserial primary key, state character varying, closed timestamp ); CREATE INDEX "state_index" ON "tickets"...
1
vote
0answers
33 views

Postgres 10.6: UPDATE on partially indexed column sometimes hangs

I have a table (6 small columns 2M+ rows) with a column dead. There are two indexes on this table, both are partial with dead = false. From time to time I want to scan the table and mark rows with ...
1
vote
1answer
69 views

JSONB vs BRIN Index

The requirements go like this: Each user has a wallet that can contain tens to a hundred or so different currencies, and for each currency an amount Number of currency can grow > 1600 (make me ...
0
votes
1answer
353 views

How do I cleanup Postgres WAL?

What is the correct way to cleanup WAL postgres? I have a database with more than 100 GB and it haves around 600 GB in pg_wal. Also I have 2 logical replications set up. Master and Slaves - Postgres ...
2
votes
1answer
29 views

How to modify values on INSERT based on referenced table, even if foreign key doesn't exist yet?

Summary I want to add rows to a (projected) very huge table, but switch 2 values for references in other tables. The catch is that the referenced tables should be updated automatically when new ...
0
votes
0answers
66 views

Very high memory usage by Postgres 10.1 on Windows 2008 R2

On Windows start up, ram usage is around 5% but it gradually keeps increasing and will eventually balloon to where it is using almost all the ram.The DB size is only 4941MB. The server has 8gb total ...
1
vote
2answers
256 views

How to restore Postgresql without pg_control but with file system backup and wal files?

OS: Ubuntu 16.04, Docker Postgresql: 10 I'm using an alternative file-system backup that is making “consistent snapshot” of the data directory every 24 hours. I'm experiencing the warning from the ...
1
vote
1answer
31 views

Suboptimal query plan when updating partitioned table

Background I have a simple CTE used to update a declaratively-partitioned table. The subquery runs quickly (1.7sec per EXPLAIN ANALYZE) and returns 3,769 records (CTE y in the query below). The ...
0
votes
0answers
23 views

Adding constraints to child tables in pg_partman

I'm trying to build a logging table and using pg_partman partitioning it on a daily level. This part is setup. The issue is I need a unique constraint on the fk_id and timestamp. The process to fill ...
0
votes
0answers
40 views

Postgres 10 partition pruning through Extended Query protocol

From what I can tell, in postgres 10, parition pruning through declaritive partitioning only works if the partition key value is included in the query explicitly SELECT * FROM partitioned_table WHERE ...
2
votes
1answer
164 views

Query to obtain weighted percentile

Trying generate SQL to compute a weighted continuous value at a given set of percentile values (the 25%,50%,and 75% levels used below, but solution should allow for an arbitrary parameter level). In ...
1
vote
1answer
52 views

What happened to pg_xlog?

There uesd to be a directory called pg_xlog that stored all of the WAL logs in PostgreSQL? Part of restoring a basebackup under the archiving scheme required me to copy the WAL into DATA_DIR/pg_xlog. ...
0
votes
2answers
312 views

Postgres jsonb vs composite type performance differences

What considerations are involved in choosing between a jsonb column and a composite type column of the same structure? For example, consider a column like that used in the Postgres documentation: ...
0
votes
0answers
20 views

Utilizing DBLINK[_EXEC] to control transactions within PL/PgSQL functions?

I am exploring options to control transactions within functions, mainly to reduce the memory footprint for large operations over multiple individual units. I.e. large data-sets are inserted regularly, ...
0
votes
1answer
68 views

archive_command failed causing master to run out of disk space. Is there a command that prioritizes master's health

I had replication set up with a simple archive_command in postgresql.conf: archive_command = 'rsync -a %p _postgresql@serverip:/var/postgresql/wal_archive/%f' Which worked great for 2+ years until ...
1
vote
0answers
17 views

Best practise for data dump 'entry point' in DB processing chain - PostgreSQL

I need to implement a process chain to transform and analyze potentially large sets of data, inserted both programmatically and manually (e.g. CSVs). In either way, the input data is standardized in ...