All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.

learn more… | top users | synonyms (1)

0
votes
1answer
212 views

Sessionizing Log Data Using SQL window functions: tweaking a query (incrementing session ID and event length)

I'm referencing to a very good blog entry, that describes how to sessionize log data with SQL window functions. I'm no database pro and I don't really understand the logic behind this query (you can ...
0
votes
0answers
3 views

Redshift Queues

I'm brand new to RedShift and am trying to debug why our systems are running very slowly. I've noticed that we have around 50 users and hundreds of daily reports running, all of which take substantial ...
1
vote
0answers
6 views

Can a BRIN index be used instead of Table Partitioning in Postgres?

Given an append only table with a GUID and a timestamp (and a bunch of other columns) which can grow by ~50Mio entries / year. I want to keep the number of indices low and just use a normal (B-Tree) ...
4
votes
2answers
3k views

PgPool 2 with HA Proxy

So, I'm looking to add high availability to a postgresql server. I've looked into some tutorials on the web but, haven't been able to find exactly what I'm looking for. E.g.: PostgreSQL ...
0
votes
0answers
12 views

PostgreSQL Redhat Cluster Implementation(Active-Passive) With Streaming Replication setup For DR failover

We have a scenario Where Customer is looking For Cluster kind of solution with DR failover Setup. I am Planning to suggest the attached Architecture with customer. Few Clarification as per ...
0
votes
1answer
114 views

When does pg_dump not export CREATE EXTENSION?

I have a production database that I am trying to dump then load (in plain text format) locally. The problem is, the database being dumped uses the hstore extension in several tables, and contrary to ...
1
vote
1answer
227 views

PostgreSQL on Windows fails to start - 'could not create shared memory segment'

PostgreSQL 9.4 & 9.5 (beta) on my Windows 7 laptop fails to start. In the Windows event log I see the following error: 2015-11-10 13:53:49 IST FATAL: could not create shared memory segment ...
0
votes
1answer
16 views

PostgreSQL identifier cross-check returns different results for qualitatively-identical query

Setup: PostgreSQL 9.3.5 I have two tables with a common identifier (propid) that is unique in one table (property) and not unique in the second table (addressstring). There are no NULL propid in ...
2
votes
1answer
30 views
+100

Increase in IO Wait after adding indexes with postgres

I've recently been put in charge of a system that has been fairly untended for a period of time. The majority of activity is via queued worker jobs based off of data feeds from other systems/data ...
0
votes
0answers
21 views

PostgresSql - Flattening nested JSON arrays

Interesting problem trying to query nested arrays stored in JSON... Context I'm using PostGreSQL and Heroku Dataclips to navigate some data I'm pulling from a server. After doing some reading, I ...
0
votes
0answers
20 views

Considerations to take into account for moving a big table into another database

In our application, we a have 67 GB table ( very big compared to the rest of our tables ) and it acts almost as an archive table since its record are not modified and the ratio of read operations is ...
0
votes
0answers
28 views
+50

Simplify Month Over Month % Change, 4 Months Moving Average %

invoice created_at | amount | status | ------------+--------+--------- 2016-06-01 | 50 | 2 | 2016-05-30 | 150 | 2 | 2016-04-29 | 32 | 2 | 2016-03-28 | ...
1
vote
1answer
256 views

pg_ctl promote is not working

I'm working on setting up a two-node PostgreSQL 9.4 environment. Right now I have test instances running on port 5532 on two identical Centos servers. node1 is correctly sending logs to a shared ...
1
vote
1answer
142 views

PostgreSQL WAL recovery not working due to missing WAL

I am using pgBarman for PITR recovery. Now it's a bit of a crisis, because the Barman backup and the WAL archive seems like it cannot be recovered. We're running PostgreSQL 9.3.6 and latest stable ...
0
votes
1answer
21 views

Long queries on PostgreSQL hot standy

Is there a way to convince PostgreSQL to execute long queries on a hot standy? It kills the query with User query might have needed to see row versions that must be removed. I understand that, but I ...
1
vote
1answer
23 views

CREATE SEQUENCE using expressions with psql variables for parameters

I've been playing with Postgres for a week or so and I'm seeing if it's possible to set the min/max values for a sequence using an expression. The specific goal is to automate create-scripts for ...
0
votes
0answers
23 views

Query that runs lighting fast in Postgres send MySQL to 100% CPU and takes forever [on hold]

I have a query that runs against a database that an industry association puts out every month. The query is complex in that it joins together 26 tables and brings back only 15 rows from among tens of ...
0
votes
0answers
19 views

Postgresql: Windows Server 2012 R2 or SuSE Linux Enterprise Server 11 SP4

I am given the possibility to chose between Windows Server 2012 R2 and SuSE Linux Enterprise Server 11 SP4, while renting a virtual server. For the needs of the server I want to create, I wish to ...
2
votes
2answers
3k views

Postgres corrupted DB recovery

Is there a way to recover data from partially damaged Postgres DB? My HDD was damaged in transport, I was able to recover most of the files but I cannot connect to the DB or use dump: I'm getting ...
3
votes
1answer
174 views

PostGIS: Cutting Holes in Self Geometry

I have a table of geometries in PostGIS, some of which overlap. When I display the geometries, this has the unfortunate consequence of blending the two stacked geometries together and producing an ...
0
votes
1answer
19 views

PostgreSQL vs MariaDb On Read/Write when table has about 200M Rows [on hold]

I want to build a new web application with my collected data, but I don't know witch DBMS is best choose for my Project, MariaDB or PostgreSQL there is 2 table : categories and data more than 200 ...
2
votes
2answers
518 views

Why postgresql pg_dump exports a view as a table?

I am using postgresql 9.3 pg_dump tool for extracting only the public schema definition using: pg_dump -s -n public -h host -U postgres --dbname=db > ./schema.sql but when I check schema.sql one ...
2
votes
1answer
29 views

Implications of many materialized views in Postgres?

I'm working on a system that includes a scheduling component, which has support for recurring events. After reading many, many posts on storing recurring events, it was suggested that they be ...
1
vote
1answer
23 views

ON CONFLICT ON CONSTRAINT fails saying constraint doesn't exist

I'm trying to use new Postgresql 9.5 upsert feature. But for some reason my query saying constraint doesn't exist (when it does). My query is this INSERT INTO journals (ext_ids, title) VALUES ...
0
votes
1answer
15 views

How to create tables with geometry in Postgres?

I need to create 2 tables with distinct geometry. One needs to be a table that has point information. The other needs to be a table that has line information. They do not need to be populated as ...
0
votes
0answers
8 views

Postgres: How to log materialized view refresh

I would like to keep a record of when various Postgres materialised views are refreshed. As far as I can see there are no built in logs. The next obvious choice is to create a trigger, however it ...
0
votes
1answer
20 views

How to create a table with data from other tables in Postgres?

How to create a table that with data from 6 other tables that have the same columns? The table names are: ab1_2, ab3, ab4, ab5 and ab6. This is a spatial data with the Postgis extension enabled so ...
1
vote
1answer
17 views

The call to LOADLIBRARY for the XA resource manager DLL failed (PostgreSQL & SQL Server)

I am attempting to setup a Linked Server from MS SQL Server 2012 to PostgreSQL 9.3 via Linked Servers & ODBC driver from PostgreSQL. Everything works, until a given query invokes MSDTC, at which ...
0
votes
0answers
15 views

How we can make “statement_timeout” work inside a function?

I have a requirement to perform a query operation in a specified time limit in postgresql. If the query is not performed in that time limit, we need to catch a exception/a action and need to kill all ...
2
votes
0answers
20 views

Could I use both UUID and Sequential ID or is it bad design

Sequential IDs have pros and cons. UUIDs have pros and cons too. Why not use both for each table? (Let's say I have -and will have- no problem about storage space) I could use: UUID for exposition, ...
0
votes
1answer
612 views

PostgreSQL: Merge rows with the same date and time but different numeric values in one row with average of the values

I am working on a financial software where we have huge table of ticks with more than 800M rows. A single row: symbol_id, broker_id, date, time, bid, ask The problem is the table has many ticks ...
0
votes
0answers
7 views

DB schema for storing the process tree of linux

I search a db schema to store the process tree of linux every half hour. Platform: PostgreSQL on Linux. I am unsure where to split all the data into columns or to use a json column. Which one is ...
2
votes
1answer
21 views

Postgres LEFT JOIN with recurring dates and users

I'm trying to get a query to show me if users have a record on a particular day, and if they don't it would return a NULL. What I have so far is a I create a sequence of days, then LEFT JOIN it, if I ...
1
vote
1answer
18 views

Storing email inbox information

I am working on a webmail client. I want to store the remote mailboxes information in a Postgres database. As a general rule of thumb, most IMAP servers will have a similar default folder structure ...
1
vote
0answers
25 views

Why is Server Configuration in Pgadmin3 inaccessible for me?

My Postgres server is running (localhost:5432), but Tools > Server Configuration is greyed out for me as below. I was attempting to access it to edit/locate my postgres.conf file.
0
votes
0answers
16 views

Is there any way to automatically keep data in a table in sync with multiple other tables without materialized views?

I would like to create some materialized views in Redshift to aggregate the information stored in several tables and keep the data in the views in sync with the source tables. Unfortunately, Amazon ...
0
votes
0answers
10 views

Postgres archive_command on Ubuntu 16.04

Ubuntu 16.04: Postgres 9.5: When i start psql -c "select pg_stop_backup();" command to execute for hot standby replica. It gives me warning as archive_command as: NOTICE: pg_stop_backup cleanup ...
0
votes
2answers
19 views

Query Amount of entries per column from multiple tables + provide table name

I have five tables with all having the same column (objart): I queried for the number of entries and combined the result with UNION, which worked well. Two questions: 1) Is there a shorter/ better ...
1
vote
0answers
19 views

psql in windows

I want to connect to Amazon Redshift and run scripts like exports to s3 and loads through command line. is psql the only way to go? If that is the case, is there a package that would install just ...
13
votes
3answers
6k views

Foreign key constraint on array member?

Suppose I have a table containing job roles: CREATE TABLE roles ( "role" character varying(80) NOT NULL, CONSTRAINT "role" PRIMARY KEY (role) ); Suppose I further have a table, users, and each ...
1
vote
1answer
110 views

postgresql 9.1 failed insert occupies increasing disk space

I have a postgresql 9.1 installation where the available disk space is very low (20Gb) however there is only one client that inserts data in various tables (typically one entry every few minutes) and ...
1
vote
0answers
21 views

Optimal query to show active records by date

I'm trying to determine what the optimal query might be for associating a date with a record that would have been active for that day. Here is a setup to demonstrate what I'm trying to do (demo at ...
0
votes
1answer
16 views

Perfomance of CREATE INDEX vs CREATE INDEX CONCURRENTLY in PostgreSQL

I know CREATE INDEX CONCURRENTLY is slower, but how many longer it will takes to finish in comparison with the traditional CREATE INDEX? 40% more or much more than that? I am creating an index in a ...
2
votes
1answer
30 views

Monitoring streaming replication

We're setting up streaming replication with one master and one (read-only) slave. We're now looking for a way to monitor the replication; mostly to verify that the slave server is still up-to-date. ...
0
votes
0answers
15 views

After crash, lost Postgresql data

I was developing a Rails application with pg when there was a power loss. After turning on my computer again I was having: psql: could not connect to server: No such file or directory. Is the ...
2
votes
1answer
114 views

Is there a way to make an OOo form for PostgreSQL updateable view?

I have a PostgreSQL database, with data from some 8 tables (not counting enums implemented as a table referenced by foreign key) summarized in a view. I made an INSTEAD OF trigger, so the view is in ...
0
votes
2answers
302 views

Whats the best and fast way to import a huge DBF file to a Postgres in Windows?

I have some huge DBF files (about 35 GB) to import to Postgres. Whats the best and fast way to do it? OBS: I have 300 GB of free disk space and 8 GB RAM.
14
votes
1answer
9k views

Can listen_addresses really be set to a list?

I have a VM with IP address 192.168.0.192 running postgreSQL. If I specify listen_addresses = '*' then I can connect from another VM at 192.168.0.191 and from localhost. But I can't seem to use a ...
0
votes
0answers
12 views

PostgreSQL user creation and prvilege setup [closed]

In MySQL I am using these commands to create user and assign the privileges: CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON newdb.* TO 'newuser'@'localhost'; ...
0
votes
1answer
142 views

Is it possible to set a timer for autovacuum in PostgreSQL

I am running PostgreSQL 9.4.1. I am currently cleaning up data, which requires me to run expensive queries. It seems like my tables get "locked" up every day at the same time in the afternoon. My ...