The Stack Overflow podcast is back! Listen to an interview with our new CEO.

Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.

Filter by
Sorted by
Tagged with
-1
votes
2answers
126 views

Gin indexing in postgres

I have four columns project_id, custom_event(boolean), page_view(boolean), event_url(text). I usually do like a regex operation in event_url column and my query will be all the four columns in that ...
-1
votes
1answer
308 views

Reduced copy of zabbix database

I've a relatively big postgresql database that tooks a lot of space in a server. The database stores a zabbix system used to monitor data that I've to safely keep, so history tables are quite big. My ...
3
votes
1answer
2k views

Best index for jsonb in Postgres

We have a table containing around 500k rows. The database table is supposed to grow to million of records. This is how the table looks like: CREATE TABLE public.influencers ( id integer NOT NULL ...
0
votes
1answer
25 views

Why and When the pg_stat_activity's most of the columns are null?

Recently, I have noticed that when I query the pg_stat_activity for few users most of the columns are [null] (pgAdmin 4) in the result. Only thing I can see is datid, datname, pid, usesysid, usename,...
0
votes
1answer
23 views

Postgres ignoring GIN index for LIKE query?

I am working in Postgres 9.4. I have a table and index like this: Table "public.title" Column | Type | ...
0
votes
1answer
45 views

Postgresql 9.6 HA issues

(Editing the question with additional information: ) We are in process of testing a Postgres 9.6 active-passive HA cluster using streaming replication on Rhel 7.5. The HA setup is done using ...
2
votes
1answer
102 views

uncommitted xmin during vacuum, what to do?

Running vacuum on two specific tables in my postgresql 10 database results in the following error: VACUUM (FULL|ANALYZE|FREEZE|) table; ERROR: uncommitted xmin 359569171 from before xid cutoff ...
1
vote
1answer
44 views

Speed up 180M+ rows returned by simple SELECT with PostgreSQL

I have performance issues on a SELECT statement which returns 180+ out of 220+ of millions of rows on my managed PostgreSQL. The table structure is the following: CREATE TABLE members ( id ...
0
votes
1answer
41 views

What are possible ways to migrate SQL Server DB to PostgreSQL DB? [closed]

Currently I am assigned to complete the task of migrating several small to large databases (from 10GB to 200GB) from SQL Server to PostgreSQL RDBMS. I would like to ask the community to share ...
0
votes
0answers
32 views

Using GROUP BY together with wildcards for table columns

Issue Is possible insert substitution for all cols of joined table? For example like this. select distinct p.product_id, i.filename, pl.* -- instead of pl.price_tax, pl.price_rec, etc. from ...
2
votes
2answers
276 views

How to dump a postgreSQL/postgis sample database filtered by a spatial extent?

My goal is to extract a subset of my postgres/gis database to create a sample dump which has to have the same data model with relations between tables and views, but which has a smaller spatial extent....
0
votes
1answer
99 views

Fastest way to check if record is related to a particular user in postgres

While implementing a sharelikes table I added a procedure to add a new record for a like. I wanted to add a check before insert that the item being liked does not belong to the users who submitted ...
1
vote
1answer
20 views

Improving distinct values estimates in Postgres

Full counts in Postgres can be slow, for reasons that are well-understood and much discussed. So, I've been using estimation techniques instead, where possible. For rows, pg_stats seems fine, for ...
1
vote
0answers
21 views

trigram index isn't used

I've created a trigram index: CREATE DATABASE people; \connect people; CREATE TABLE people ( id bigserial NOT NULL, name text NULL ); CREATE EXTENSION fuzzystrmatch; CREATE ...
12
votes
5answers
5k views

How to examine PostgreSQL server's SSL certificate?

Suppose there is a PostgreSQL server running and it has SSL enabled. Using "standard" Linux and PostgreSQL tools, how can I examine its SSL certificate? I'm hoping for output similar to what you ...
0
votes
1answer
404 views

Best way to create a daily snapshotted read-only db in RDS?

We have a PostgreSQL db instance in RDS. In our application we need to run a daily export in the early hours of every day, the export and transformation may take some time and one issue we've found is ...
0
votes
0answers
25 views

Why can every PostgreSQL user see all databases and their tables? [duplicate]

I'm running a PostgreSQL database server, version 10 on Linux, for multiple users. Today I found out by surprise that any user can see all other users' databases and their tables, columns and other ...
0
votes
0answers
24 views

Instant Messaging Schema design advice

I'm trying to build an Instant Messaging functionality in my app as part a bigger project. Chats can have more than 2 participants (group chats) If participant A delete a message, it still should be ...
-1
votes
1answer
22 views

trigram index for integer field

I am trying to optimize query with the clause: WHERE LOWER(CAST(\"my_table\".\"id\" as TEXT) LIKE '%456%' I decided to create a trigram index for it. CREATE INDEX index_name ON my_table USING gin (...
0
votes
1answer
73 views

Upgrade Postgresql from 9.6 to 11 with different data directory

I am trying to upgrade PostgreSQL version from 9.6 to 11. I am following this link. The problem is I have changed my default location of PostgreSQL data directory from /var/lib/postgresql/ to /opt/...
8
votes
1answer
681 views

Does UPDATE without WHERE clause lock a table in PostgreSQL?

Does the whole table UPDATE (without specifying WHERE clause) lock a table in PostgreSQL? E.g. does it prevents rows from being deleted / inserted? E.g. if I run UPDATE t1 SET key = 'value' Can I ...
3
votes
1answer
25k views

psql: fatal database does not exist error

I have a linux user account, postgresql role, and associated database, all called foo. from the linux user foo, I ran the command dropdb foo Now when I try to run psql from the linux user foo, I ...
1
vote
1answer
34 views

What is the performance cost of enabling “log_lock_waits” on Postgres?

I am trying to understand what is the performance cost of enabling log_lock_waits on Postgres. As per this documentation, while log_lock_waits = on: Controls whether a log message is produced when ...
0
votes
1answer
32 views

Strategy for making Postgres support multiple versions of client code

I'm about to embark on an overhaul of our code for pushing to Postgres, and want to solicit some feedback before going on. The setup here is that we've got a lot of software deployed with a non-...
2
votes
1answer
313 views

Hashtags and synonyms table design

So what I'm trying to do is setup a tag and synonyms structure. Where you can assign a tag as a synonym to another tag. E.g for tag: photographer I'd want to add 'photography, photographers, photo' ...
1
vote
1answer
417 views

Recursive query to find shortest path in graph

I have an assignment to find the shortest possible path between two people in a table. The table is Company(company_name, job, name), all of them not null, but none of them unique (primary key is the ...
0
votes
1answer
1k views

Possible to drop table ignoring dependencies?

I had a table from which I wanted to delete a lot of rows but that was failing because of memory constraints. I saw a suggestion to "simply" copy all the rows that I don't want to delete into a new ...
0
votes
0answers
16 views

Master to master using logical replication [duplicate]

Is it possible to create master to master syncing architecture using logical replication in postgres? I have a database at local machine and I want to sync data from local to remote/cloud and from ...
1
vote
1answer
353 views

Streaming replication archive folder taking all space- Postgresql- 9.4

We had recently setup postgres streaming replication between master (physical, CentOS release 6.6) and slave(virtual, CentOS 7.6). We did the test and streaming replication is working fine. We ...
0
votes
1answer
107 views

How to convert timestamp with time zone to numeric (unix) in PostgreSQL?

I have timestamps with time zone (timestamptz) in my table. Can I convert them to numeric or int in unix timestamp format? I found this question, but it shows only how to convert timestamp without ...
1
vote
1answer
617 views

Access denied error when archiving WAL during postgresql 9.5 replication setup

I want to setup Postgres replication on Windows. I followed the given youtube url step by step: youtube video. For archive_command I use this: archive_command = 'copy "%p" "\\\\ip-address of ...
0
votes
1answer
298 views

Can any one Explain me What is the Use of Cascading Replication in Postgresql?

What are the advantages of cascading replication in PostgreSQL. I have done Testing On this With three nodes. M1---->S1----->S2 What is the Use if My master(M1) goes Down(failover). Where the ...
0
votes
1answer
219 views

Why is my postgres `base` directory filling up rapidly?

My pg_xlog directory was filling up because my standby server was down. This brought the volume to 100% usage and shut down postgres. I brought up the standby servers, moved pg_xlog to another volume, ...
0
votes
1answer
669 views

Postgresql - archive_command Windows, not working

I'm attempting to set up archiving on a windows server machine but when I check the logs nothing is happening. The postgresql.conf file this is my input: #wal_level = replica #archive_mode = on #...
2
votes
1answer
55 views

Slow Postgres Aggregate Query

I need to calculate values for a series of measurement data. The measurement data is divided into 1000 groups of consecutive rows as each call of the query must output a series of 1000 values. Typical ...
0
votes
1answer
195 views

Merge jsonb objects and sum their values over multiple rows

I save visits to my database. In my database I have a table with aggregations of visits called visits_agg. This table has one row per hour per hostname. Both are fields in the table which will be used ...
14
votes
1answer
34k views

Size limit of character varying Postgresql

What is the size limit of various data types in postgresql? I saw somewhere that for character varying(n), varchar(n) n must be between 1 to 10485760. Is that true? What are the valid sizes for ...
0
votes
1answer
36 views

Is there a way to have SQL work in parallelism with two computers?

Amateur here. I have a couple of the same old 4 core computers that I am trying to use to beef up some automatic query scripts that run every evening for my hobby. I recently found MPICH to have the ...
0
votes
1answer
281 views

Pgpool - 3.5 configuration

[enterprisedb@pgdvdb1dr bin]$ 2017-06-20 11:34:41: pid 103313: WARNING: could not open configuration file: "pgpool.conf" 2017-06-20 11:34:41: pid 103313: DETAIL: using default configuration ...
2
votes
1answer
38 views

Postgres: Why does pg_stat_get_dead_tuples return 0 when there is still wasted space?

Using Postgres 11.1, I have been trying to determine/estimate the amount of 'wasted' space in a table that would be recovered by a VACUUM FULL. My plan was to use pg_stat_get_live_tuples (L), ...
2
votes
3answers
116 views

Query to get counts of values per column

I have a big table of vendor-supplied data (that I can't change around much) with about 315 columns. I suspect that many of the columns are not being used (or at least not consistently). I'd like a ...
0
votes
2answers
33 views

How to do a cumulative count for certain values

I have a scenario to do cumulative counts in certain way. So I decided to create an experiment for that before I run the query in the actual environment. I have created a table as: create table ...
2
votes
1answer
36 views

How to select same number of data points over different time ranges

Grafana is querying our postgres database using this SELECT statement: SELECT tstamp AS "time" dvalue AS "weight" FROM tablename WHERE description = "exp_weight" AND tstamp BETWEEN 'time1' ...
5
votes
1answer
1k views

How to exclude tables when using pg_dumpall with postgresql?

If use pg_dump, it has a --exclude-table-data option to ignore special table. But pg_dumpall command doesn't has that option. So how to exclude several tables when dump all databases?
34
votes
4answers
15k views

Monitoring Progress of Index Construction in PostgreSQL

Is there a way to monitor the progress of the creation of an index in PostgreSQL. I am creating an index on a large table and I would like to see how fast this is occurring. Is there a way to monitor ...
0
votes
2answers
19 views

Postgres lowering max_connection

I have a question regarding max_connections in postgresql.conf. In the Postgresql documentation, it says it determines the maximum number of concurrent connections to the database server. Does it ...
0
votes
1answer
29 views

How to find similar records using multidimensional array?

I have table that describes a movie release. This table consists of columns: movie_id int release_day int showtime_count int ticket_sold_count int e.g. (1, 1, 100, 200) tuple describes movie ID 1, ...
0
votes
1answer
95 views

SELECT row based on range from columns in second table

I want to SELECT all rows from first_table INNER JOIN second_table for values where the column in first_table fall within range of values from two columns in second_table. As example, I have created ...
4
votes
3answers
2k views

Postgres to Postgres DBLink with AWS/RDS

I am trying to use dblink functionality for Postgres 9.5.2 DB to Postgres 9.52 on AWS/RDS. On the server: CREATE SERVER myremotedb FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'myremotedb....
1
vote
1answer
25 views

Any ideas what is wrong with this code, Error at or near Insert on a new function

CREATE FUNCTION insert_m_a_k("odOrganizacijaId" uuid, "doOrganizacijaId" uuid) RETURNS boolean AS $BODY$BEGIN INSERT INTO mapper_a_k (id, model_a_k_id, organizacija_id, k_a_k, k_a_k_ime, aktiven, ...