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
25 views

Cannot delete Postgresql constraints that were former unique keys

I have modified unique constraints. I am adding a field to every unique constraint. To do that, I need to drop the existing unique constraints and create the new ones. I am trying to delete foreign ...
1
vote
1answer
120 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
1answer
28 views

Postgresql log rotation

In postgresql, I'm using the following configuration to maintain my weekly logs: log_truncate_on_rotation = on log_rotation_age = 1d log_filename = 'postgresql-%a.log' log_rotation_size = 0 #just ...
1
vote
0answers
25 views

Windows Domain User with Admin privileges cannot access a drive with full permissions in psql

I have installed the standard PostgreSQL 9.5 from Enterprise DB with a domain-level postgres user who has domain-level admin group membership. I have explicitly added full permissions for this user to ...
0
votes
0answers
25 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
137 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
199 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
0answers
22 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 ...
0
votes
1answer
35 views

Database extensions vs migrations [closed]

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
129 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
1answer
80 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
49 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
456 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
30 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
143 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
30 views

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
317 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
26 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
341 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
32 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
278 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
187 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
46 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 ...
-1
votes
2answers
86 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
68 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
22 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 ...
7
votes
2answers
12k 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
54 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
1answer
50 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
82 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
160 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
284 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
328 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
60 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
380 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
155 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). ...
7
votes
1answer
80 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
38 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
26 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
vote
0answers
21 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 ...
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
47 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
19 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 ...
3
votes
1answer
39 views

Postgresql tables seem duplicated (with suffixes such us pkey, hashes, etc)

due to some problems I came to check the state of my current database in production and I found that some tables are "duplicated". By duplicated I mean that there appear to be some clones of the ...
0
votes
0answers
29 views

Restore PostgreSQL database with same locale settings as source database

I have a PostreSQL 9.x database on an Ubuntu 14.04 LTS production machine. My development machine is Windows 7-based, providing PostreSQL 9.y. I want to restore the Ubuntu PostreSQL database on my ...
0
votes
1answer
172 views

Tuning postgresql.conf when benchmark

Description: We build a new database server Enterprisedb v9.3 (PostgreSQL commercial), so we use JMX to benchmark it with 1000 users. Here our detailed information: Operating System: Centos 6 x64 ...
3
votes
1answer
26 views

UPDATE a column in one table according to a column in another

In Postgres, I have two tables that have become de-normalized. We're in the process of migrating to a normalized form, but in the meantime the tables have become out of sync. I'd like to copy the ...
1
vote
4answers
110 views

Find IDs from a list that don't exist in a table

Say I have the following schema and data: create table images( id int not null ); insert into images values(1), (2), (3), (4), (6), (8); I want to perform a query like: select id from images ...
0
votes
1answer
22 views

Postgres: extracting multiple bit-columns from single 'flags' column after join with 2nd table

I have a query similar to this below. As you can see, there are multiple parts which are very similar. SELECT id, aaaa, bbbb, tags ,( SELECT COUNT(*) > 0 ...