Stack Overflow
Stack Overflow

Questions tagged [postgresql-10]

for PostgreSQL questions specific to version 10

On 5 October 2017, PostgreSQL 10.0 was released. Major enhancements in PostgreSQL 10 include:

  • Logical replication using publish/subscribe
  • Declarative table partitioning
  • Improved query parallelism
  • Significant general performance improvements
  • Stronger password authentication based on SCRAM-SHA-256
  • Improved monitoring and control

Note that as of version 10, PostgreSQL has moved from 3 component version ids to 2 components, so 10.1 is a minor release.

The official documentation for this version is available at: http://www.postgresql.org/docs/10/static/index.html

632 questions
2
votes
1 answer

running automated Postgres backups on Linux

I'm using the scripts from here: https://wiki.postgresql.org/wiki/Automated_Backup_on_Linux I have changed the owner of the files to "postgres" and I tried running the backup script while being logged in as "postgres" my pg_hba.conf contains the…
Arya
  • 8,473
  • 27
  • 105
  • 175
2
votes
1 answer

How does PostgreSQL handle temp table ON COMMIT DROP in the face of multiple threads?

Per the documentation: ON COMMIT DROP: The temporary table will be dropped at the end of the current transaction block. However, temporary tables are unique per session, not per thread: see here My question: If a temp table is marked WITH ON…
IamIC
  • 17,747
  • 20
  • 91
  • 154
2
votes
0 answers

Migrating PostgreSQL 9.5 to 10 - Foreign key, Primary key

I am migrating my Postgresql 9.5 to Postgresql 10 I have also a Visual studio .net component (npgsql) integration that helps me to connect and to use Entity Framework with .NET. I am forced to use partitioning too on two large (huge) tables I…
Gabriele D'Onufrio
  • 405
  • 1
  • 5
  • 17
2
votes
1 answer

How many iterations does the scram-sha-256 setting use in PostgreSQL 10?

How many iterations does the scram-sha-256 setting use in PostgreSQL 10? The docs just say Setting this parameter to scram-sha-256 will encrypt the password with SCRAM-SHA-256. The build log reads. Add SCRAM-SHA-256 support for password…
Evan Carroll
  • 78,363
  • 46
  • 261
  • 468
1
vote
0 answers

Postgres, create index for WHERE ... OR .. case without rebuilding a query

I have a query(Posgres) that looks like this (I replaced fields with * meaning that fields could vary): SELECT * FROM actions WHERE (logs."group"=$1 OR logs."team" IN ($2)) and actions.datetime::date=(timezone('UTC', now()) +…
developer
  • 19
  • 3
1
vote
1 answer

How can I fetch the first 7 days with scheduled lessons for a user in the future using a SQL query?

I have a database table as follows: id lesson_start lesson_end instructor_id student_id 1 2023-06-01 04:00:00.000000 2023-06-01 06:00:00.000000 3 4 2 2023-03-18 11:00:00.000000 2023-03-18 12:30:00.000000 3 4 ... ... I want…
Milan Dol
  • 15
  • 6
1
vote
1 answer

Why does `date_trunc('day', current_date) + interval '1 day' - interval '1 second'` cause query to hang?

When I set up a date range using max(lasttime) for the upper bound, the query works. range_values as ( select date_trunc('month', current_date) as minval, max(lasttime) as maxval from people ) When I use date_trunc('day',…
3ch01c
  • 2,336
  • 2
  • 17
  • 15
1
vote
1 answer

DC-DR Sync issue in Patroni PostgreSQL setup

We have a 3 instance cluster in DC and a 3 instance cluster in DR as standby_cluster. For DC-DR real-time sync, we have added the DC cluster's leader's IP as standby_cluster IP in the DR patroni config. which is working fine and we are getting…
Rahi Shah
  • 11
  • 1
1
vote
0 answers

How to prevent special characters from being corrupted when using UTF8 encoding in Postgres?

I'm using PostgreSQL DB with UTF8 encoding and I'm trying to fetch some data using psql.exe. My table diacritics contains the following data: id name 1 Kočička 2 Mňau As you can see, there are a few (Czech) characters with diacritics.…
dva
  • 303
  • 1
  • 2
  • 12
1
vote
1 answer

How to migrate a PostgreSQL 10 database from Windows C drive to another drive

I have almost an identical problem as this post: How to migrate a Windows 10 installation of PostgreSQL 9.5.7 to a larger disk I have a PostgreSQL database on my C drive which is running out of space. I want to move my database to my larger F drive.…
Jake Lyle
  • 11
  • 1
1
vote
0 answers

Postgres upsert

I am trying to do update my database tables via upserts from a postgres-to-postgres foreign data wrapper. I have two code snippets below. The first is using the constraint on the primary key and the latter is using the actual primary key column. The…
onetrickpony11
  • 37
  • 4
1
vote
0 answers

No data available in performance insight

I have a critical application deployed in AWS RDS; the DB engine is PostgreSQL version 10.18. The architecture is unusual, because we're talking about medical data. This means that all the doctors connecting the database (through a PGBouncer) have…
Federico Loro
  • 65
  • 1
  • 6
1
vote
0 answers

How to programmatically collect Postgres query performance metrics

Problem statement I have two databases provisioned via Heroku’s PostgreSQL database-as-a-service add-on. These databases are running PostgreSQL 10.19. Heroku is deprecating PostgreSQL 10 later this year. I therefore plan to upgrade these databases…
cjrpostma
  • 11
  • 1
1
vote
0 answers

Discrepancy between two (seemingly) identical cache-hit-ratio queries

I'm trying to assess the performance of a Postgres 10.6 instance. The first query below returns 0.88, the second one 0.77. Whence the discrepancy between these two queries for the cache hit-ratio? SELECT sum(blks_hit) / sum(blks_hit +…
VanillaDonuts
  • 135
  • 7
1
vote
1 answer

Postgres "invalid input syntax for type oid:"

I am using Postgres 10 on Centos (PostgreSQL 10.17 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit) When I execute the SQL statement below I receive the error "invalid input syntax for type oid:" SELECT…
norris-md
  • 306
  • 2
  • 10
1 2 3
…
42 43