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
0answers
11 views

Optimizing “WHERE x BETWEEN a AND b GROUP BY y” query

SELECT SUM("intCol"), "textCol" FROM "test_table" WHERE "timestampCol" BETWEEN '2016-06-12' AND '2016-06-17' GROUP BY "textCol" Please correct me where my understanding is incorrect. In Postgres, I ...
0
votes
0answers
8 views

Create custom JSON response from PostgreSQL table

Consider the following table: CREATE TABLE IF NOT EXISTS trips ( trip_id SERIAL PRIMARY KEY, start_location TEXT REFERENCES locations(label), end_location TEXT REFERENCES locations(label), ...
0
votes
1answer
119 views

postgresql - How to limit access to specific database only, and restrict access to system tables

I have a user test, which can view data from the system tables such as pg_class. The idea is to isolate him to have access to a specific database only and not have any access to system resources. The ...
1
vote
1answer
168 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 ...
1
vote
0answers
11 views

Median absolute deviation for time series outlier detection in Amazon Redshift

Context I am tasked with trying to detect outliers in time series data in an Amazon Redshift (PostgreSQL) system. Also known as a public holiday detector around the office. The method I have been ...
0
votes
0answers
9 views

BDR Syncing Logs

I am making use of 2ndquadrant's BDR. Syncing always worked smoothly except for one day when I would make data changes on node 1. The data was confirmed to be on the node and committed successfully ...
1
vote
0answers
9 views

Postgres latency issues during memory compaction on EC2 (Ubuntu 14.04)

We've recently upgraded our EC2 instance that hosts our Postgres database to an i2.8xlarge with 244GB of memory (this is to utilise the large amounts of ephemeral storage it comes with). Since ...
2
votes
0answers
12 views

What can I do to monitor “out of shared memory” issues?

I'm new to database administration and I would really like some advice on what tools I could use or what I could do to monitor "out of shared memory" issues. I've been seeing these "out of shared ...
0
votes
1answer
32 views

Database extensions vs migrations [on hold]

When using of database specific features (instead of 3rd party solutions) is justified, especially on the beginning stage of the project? Example: Postgres has full-text search, which do not support ...
1
vote
1answer
100 views

Change AWS's Read Replica's streaming replication to trigger based replication

We have a Postgres RDS instance for which we have created a read replica instance. The problem we are facing is that, that whenever there is a long running query(about 30-40 mins) on Read Replica, it ...
2
votes
0answers
31 views

In Postgresql comparing timestamps gives no result at all?

I am having two tables a, b. Table a is a FDW table from a mysql database. Table b is a normal pgsql table. b.entry_time is of type timestamp without time zone. table a is having some data that I ...
1
vote
1answer
66 views

What's an “inline query”?

According to the currently accepted answer to this question a partial solution to an efficiency problem was to "inline" the query. I can't tell what that is, I can't ask in comments (too little rep) ...
1
vote
1answer
22 views

PostgreSQL to HDFS to Spark, or PostgreSQL directly to Spark SQL?

If the end goal is to perform computations with Spark, what are the reasons one would first transfer Postgres data to an HDFS (with Sqoop) vs. just using Spark SQL with Postgres directly (with the ...
0
votes
2answers
387 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.
2
votes
0answers
17 views

export data from postgresql

I have some text like xml -code in a field of table. I want to export the data from my field into file without any changes. I've tried to use a lot of variants but all of them changes the text. The ...
0
votes
1answer
117 views

PostgreSQL : Master-Slave replication strategy and solution for 3 servers.

We have currently 3 servers(X,Y,Z) running Debian X64, two of them host projects(Y,Z), which don't require that much database, as they serve more static content, but the third server(X) is largely ...
0
votes
1answer
24 views
+50

Get the Replication/Lag time of Redshift

I'm currently running RedShift with pushes of all data from our production Postgresql databases every 10 minutes or so. Periodically, the ETL process from Postgresql to Redshift gets delayed or backed ...
1
vote
1answer
274 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
0answers
19 views

Repmgr standby does not ask witness before promote

we have PostgreSQL 9.5 cluster with 3 servers (master,standby, witness) and repmgr 3.1.3. Master and standby server are in different data centers. Set automatic failover. All working good except one ...
1
vote
1answer
309 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
2answers
26 views

Routinely deleting aged postgresql rows via cron

I have a Django website with a postgresql 9.3.10 backend. The website contains dynamic user-generated content (think of it as a form of 9gag). There are some tables from which I routinely delete aged ...
5
votes
2answers
5k views

Have Postgresql accept 1 and 0 as true and false for boolean

I have a large file of dumped sql data to load in to postgresl (600mb+). In that dump, fields that are boolean true and false are expessed as 1 and 0 without quotes. Trying to load them in to ...
9
votes
2answers
267 views

Find rows where integer sequence contains a given subsequence

Problem Note: I refer to the mathematical sequences, not the sequences mechanism of PostgreSQL. I have a table representing sequences of integers. The definition is: CREATE TABLE sequences ( id ...
3
votes
1answer
184 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
30 views

Using postgres database from another computer

I have PostgreSQL version 9.5 installed at my machine (Windows 7 Enterprise x64 bit version). I have several databases in schema 'public'. I would like to use/access one of these databases from ...
0
votes
0answers
24 views

PostgreSQL Numeric field: allow comma or dot in decimal separator [on hold]

Some users insert a comma (1,2) instead of dot (1.2) in a numeric field. This raises a postgresql error: Invalid input syntax for type numeric I tried creating a trigger that replaces the comma ...
-2
votes
2answers
65 views

SQL Text data type deprecated?

I have read that Microsoft SQL Server has deprecated the text data type in favour of varchar(max). PostGreSQL already allows varchar (without a length) as an alternative for text. The question is, ...
1
vote
0answers
43 views

PostgreSQL Partitioned table update trigger

I have a date-partitioned table (inheritance partitioninng just like the one pg_partman does) which is referenced by quite a few tables in my database. To resolve the FKs checking for referenced field ...
0
votes
1answer
19 views

How to add PK on every id column in Postgres

I have an issue. I have database backup for existing DB let's call it dev_db. I recovered it but under a different name like master_db and backup how I understood has only tables, columns types and ...
6
votes
2answers
11k views

How to upgrade postgresql 8.4 to 9.4

I want to upgrade my postgresql 8.4 to 9.4. the documentations are not clear to me. Will I lose my old databases if I do the upgrade? How can I backup my old databases if I am to lose them after ...
1
vote
2answers
52 views

pgAdmin ||| paging?

I'm wondering if there is no paging feature for the pgAdmin III 1.22.1 Query tool. So if I try to query a table with 500k+ rows without constraints, the query takes like 30s. Is there a paging ...
0
votes
0answers
37 views

DBMS comparison [on hold]

I am interested in getting to know differences in some of the DBM systems. I am mainly thinking of MS SQL, PostgreSQL and mySQL, but am also willing to dig into some other DBM systems, like Oracle. ...
0
votes
1answer
31 views

Repopulating PostgreSQL Database with Data Directory from Another DataBase Instance

I have had to delete PostgreSQL for an issue and reinstall it, however before uninstalling I did not execute the command pg_dump. I still have the data directory from this instance. After reinstalling ...
1
vote
0answers
26 views

Postgres “could not receive data from client: Connection reset by peer.”

I have a cakephproject got a error when the project reload and it seem the error Database connection "Postgres" is missing, or could not be created. I have checked the error it will show error could ...
0
votes
1answer
153 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 ...
6
votes
2answers
257 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
2answers
303 views

Postgresql Chained Slaves and Slave Promotion

So I have a setup like Master -> Slave1 -> Slave2. Slave1 replicates from Master and Slave2 replicates from Slave1. If I promote Slave1 to master is it possible for Slave2 to continue replicating from ...
3
votes
0answers
51 views

Protect a table against concurrent data changes while allowing to vacuum it

I am running PostgreSQL 9.5. In a transaction, I check if data in the table satisfy some conditions: IF condition1 THEN -- do this ELSE IF condition2 THEN -- do that ELSE ... END IF; ...
0
votes
1answer
340 views

sorting by partial key name in json column

I have column document with data type json. I save here data for logging. So document has: {blocks: { participant-information_primary__Primary__39011__2288960: first_name: Firsst name, ......
1
vote
1answer
117 views

Querying JSONB in PostgreSQL

I have a table, persons, which contains two columns, an id and a JSONB-based data column (this table has just been made for demonstrational purposes to play around with PostgreSQL's JSON support). ...
0
votes
2answers
198 views

SQLAlchemy connection error with Postgres

I am trying to write a basic python script that uses SQLAlchemy to connect to my Postgres database. In the connection details, if I use "LOCALHOST", it connects fine. If I reference the server by name,...
7
votes
1answer
64 views

Who is “focker” and why does he own my schema?

I have a Postgres DB deployment in Compose.io, and I am using the standard "compose" db for my app. I would like to wipe out the current DB contents so that I can recreate the DB with a new schema. ...
1
vote
1answer
33 views

PostgreSQL merging identical tables from separate databases into single database

I've started working on a project where the application data is split across multiple databases that all have the same schema (basically there's a database per "organisation"). After some discussion ...
0
votes
1answer
23 views

Postgres: turn off formating for xml export?

I want to export some table to xml file: \o output.xml SELECT table_to_xml('modified_records', TRUE, TRUE, ''); However, I'm struggling with psql formatting. The best I managed to get is: \t on \x ...
-1
votes
0answers
14 views

How can i get created function dates in postgresql? [closed]

I want to know about newly function created date in postgresql.
1
vote
0answers
15 views

Add Shapefile loader plugin to PgAdminPortable

I'm using PgAdminPortable and I'd like to enable « PostGIS Shapefile and DBF loader » plugin. The issue is that the files are not structured as in a PostgreSQL complete install (see below). I have ...
1
vote
1answer
153 views

How to measure or estimate the streaming replication lag in Postgres 9.2

I've been asked to reverse engineer an app that uses postgresql in the back end. I can see that there is some database replica tion going on... based on my reading, I *think it's called streaming ...
0
votes
0answers
38 views

Remove header (column names) from query result [migrated]

I am using a Java based program and I am writing a simple select query inside that program to retrieve data from the PostgreSQL database. The data come with the header which is an error for the rest ...
3
votes
1answer
32 views

Customize jsonb key sort order involving arrays

I have a table in PostgreSQL with some data: create table t2 ( key jsonb, value jsonb ); INSERT INTO t2(key, value) VALUES ('1', '"test 1"') ,('2', '"test 2"') ,('3', '"test 3"') ,('[]', '"...
0
votes
0answers
12 views

RedShift Roles For Superuser-like Role with Read Only Access

I'm managing a RedShift cluster and our engineers need more access than is generally provided to them with their roles. Essentially they would require the right to read EVERYTHING in all tables but ...