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

Restore Postgres Database with Old Data Directory

I have not made a backup from my old data directory, but I still have the directory in tact. In my current database I have the same Schema, etc... When I try to restore the database (using pgadmin III)...
0
votes
1answer
73 views

CTE Query copy value if exists into an existing table [closed]

I got the following query: WITH table_data AS ( SELECT UNNEST(array['Small Bus', 'Window', 'Test 1']) AS description, UNNEST(array['1dfsdf4', 'QSF-312', 'EXD-TUH']) ...
0
votes
1answer
21 views

Can I pass a TABLELIKE parameter to a PostgreSQL FUNCTION/PROCEDURE?

I have a function which returns: TABLE (id TEXT, collected TIMESTAMPTZ) In another function, I would like to be able to pass the result of that first function as a parameter. For example: CREATE ...
1
vote
1answer
39 views

Impact of OS timezone change on Postgres Database

I want change OS time zone to UTC. This will shift back time by 2h. Can this possibly damage data in any way (for example overwrite data)? What should I do to safely change the OS time zone?
0
votes
0answers
41 views

Deadlock during delete in same transaction?

pid basename oid user tx locktype acquired date time query 7592 base 1595014 postgres 363/197025 RowExclusiveLock Yes 2016-06-27 18:19:54+...
0
votes
1answer
28 views

Incremental backup in postgresql in windows

I am developing an application with postgresql as backend. So my requirement is to take the incremental backup either hourly or daily basis. I have gone through many related articles (like https://...
1
vote
1answer
128 views

Good encryption performance

I'm converting a sql server database to postgresql 9.4. I'm stuck on something that is really really slow. I use copy to bulk insert 200 files of 5000 lines each into 200 tables (one file per table). ...
1
vote
1answer
155 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 ...
3
votes
2answers
206 views

Limiting read access in postgres to few rows

We have a requirement where we want to limit the maximum no. of rows that a select query can return when its done using a particular login. So basically we would like to append a limit clause to any ...
0
votes
1answer
19 views

postgresql statement taking ages to cancel in pgAdmin III

I was running a query in pgAdmin III and it was taking a long time. After 9 minutes I decided to cancel it using the stop button. The UI stated that it was cancelling and stayed in that state for ...
2
votes
1answer
121 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 ...
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
1answer
224 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
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
23 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
19 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
195 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
21 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
120 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
79 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
45 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
449 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
29 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
137 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
305 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
24 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
336 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
31 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
275 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
186 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
43 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 ...
-2
votes
2answers
82 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
65 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
60 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
159 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
281 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
323 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
58 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
373 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
145 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). ...