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
46
votes
2 answers

How to migrate an existing Postgres Table to partitioned table as transparently as possible?

I have an existing table in a postgres-DB. For the sake of demonstration, this is how it looks like: create table myTable( forDate date not null, key2 int not null, value int not null, primary key (forDate, key2) ); insert into…
yankee
  • 38,872
  • 15
  • 103
  • 162
24
votes
5 answers

postgresql pg_dump without schema name

pg_dump version 10.3 started adding the schema name explicitly to the table name. So what used to be INSERT INTO table (id) VALUES (1); Is now INSERT INTO public.table (id) VALUES (1); How can you switch it off? My problem is, that I dump this…
raumi75
  • 341
  • 1
  • 2
  • 4
23
votes
1 answer

Duration of PostgreSQL ALTER COLUMN TYPE int to bigint

Let's say I have a table that has an id that is an INTEGER GENERATED BY DEFAULT AS IDENTITY I'm looking to document how to change the type, if in the future an integer is too small and I need to change the id type from integer to bigint. I'm mainly…
Deniablesummer
  • 260
  • 1
  • 2
  • 11
21
votes
1 answer

Postgres 10.3: SELECT queries hang for hours

My application is using Postgres as DBMS, the version of Postgres that i'm using is 10.3 with the extension Postgis installed. Occasionally i noticed that in random interval of times the dbms become slow and get stuck on a few SELECT queries. From…
Riccardo
  • 1,490
  • 2
  • 12
  • 22
20
votes
1 answer

Postgres Psycopg2 Create Table

I am new to Postgres and Python. I try to create a simple user table but I don't know why it isn't created. The error message doesn't appear, #!/usr/bin/python import psycopg2 try: conn = psycopg2.connect(database =…
Ricardo Pinto
  • 333
  • 1
  • 2
  • 11
20
votes
2 answers

manually create replication slot for publication in PostgreSQL 10

I am trying to get a stream of updates for certain tables from my PostgreSQL database. The regular way of getting all updates looks like this: You create a logical replication slot pg_create_logical_replication_slot('my_slot', 'wal2json'); And…
George
  • 988
  • 2
  • 10
  • 25
16
votes
4 answers

Google Cloud SQL Postgres, when will PG 10 be available?

I am planning on moving our main project to Postgres 10 at some point. I like to keep the local dev's database version close to what we are running on prod. Currently our prod database is on Google Cloud SQL PostgreSQL 9.6. I have not heard…
xDTim
  • 161
  • 1
  • 3
16
votes
2 answers

Space required for VACUUM FULL table

From the PostgreSQL 10.4 manual regarding a full vacuum: Note that they also temporarily use extra disk space approximately equal to the size of the table, since the old copies of the table and indexes can't be released until the new ones are…
Thrasi
  • 418
  • 1
  • 5
  • 15
15
votes
2 answers

How to use scram-sha-256 in Postgres 10 in Debian? Getting "FATAL: password authentication failed"

I edited pg_hba.conf: sudo su postgres nano /etc/postgresql/10/main/pg_hba.conf and added this line: local all username scram-sha-256 and changed all md5 to scram-sha-256 in that file. As the postgres…
mYnDstrEAm
  • 751
  • 2
  • 8
  • 26
15
votes
3 answers

how to use COMMIT and ROLLBACK in a PostgreSQL function

I am using three insert statements, and if there is an error in the third statement, I want to rollback the first and the second one. If there is no way to do this, please tell me a different approach to handle this in PostgresqQL. If I use COMMIT…
Rahul Gour
  • 487
  • 2
  • 7
  • 21
13
votes
3 answers

Get table size of partitioned table (Postgres 10+)

I came across this query on Postgres weekly which shows tables, their sizes, toast sizes and index sizes in bytes: SELECT relname AS table_name, pg_size_pretty(pg_total_relation_size(relid)) AS total, pg_size_pretty(pg_relation_size(relid)) AS…
Damir Ciganović-Janković
  • 821
  • 2
  • 12
  • 30
12
votes
1 answer

What is the most suitable hibernate.dialect for postgresql 10.5?

I am looking at hibernate documentation here: https://docs.jboss.org/hibernate/orm/5.0/javadocs/org/hibernate/dialect/package-summary.html to try to find which hibernate.dialect to use inside persistence.xml It does not mention a class for Postgres…
Leon
  • 408
  • 1
  • 6
  • 17
11
votes
3 answers

Postgresql 10 - Parallel configuration

There are 4 configurations to enable the parallel and do the optimization, but the documentation of PostgreSQL doesn't says anything about values or calculation. My questions are: 1- How to calculate the values of max_parallel_workers, …
HudsonPH
  • 1,838
  • 2
  • 22
  • 38
9
votes
2 answers

Upgrade to postgres 10 wanting to use checksums

I have a postgresql 9.3 instance that I am wanting to upgrade to postgresql 10. I have a command that I think should work but it includes checksums in it. My old instance doesn't have checksums enabled (could it even be at all? Forget was too long…
user9753902
9
votes
3 answers

PostgreSql : ERROR: relation "sequence" does not exist while restoring from dump file

I get the following error while restoring database from dump file on server: ERROR: relation "table_id_seq" does not exist LINE 1: SELECT pg_catalog.setval('table_id_seq', 362, true); my local psql version is 10.2 server psql version is…
Saly
  • 1,446
  • 3
  • 11
  • 18
1
2 3
…
42 43