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

learn more… | top users | synonyms (1)

1
vote
0answers
103 views

Postgresql service run failure

Trying to setup replication through the wal shipping using Postgresql 9.3 x64. Used this instruction from youtube. The problem is - the slave postgresql service won't run. This is what in logs ...
0
votes
1answer
107 views

Moving Local PostgreSQL 8.4.20 Database to Amazon EC2 or RDS

I'm running PostgresSQL 8.4.20 on a local server and plan to migrate the data to either Amazon EC2 or RDS. I have working knowledge of databases and need advice from experts. I've been researching ...
0
votes
0answers
82 views

Getting PAM-compatible password hashes from pgcrypto

I want to keep user records in Postgres database and faced one issue which block me. It is password encryption. For example, this is correcetly encrypted password 12345 via default Ubuntu crypt ...
1
vote
1answer
846 views

Slow max, min query in Postgresql View

I have a view and it's a left join between two tables (table_a has hundreds of millions of rows, table_b has ~2 millions). The query SELECT MIN("id") AS "min_id",MAX("id") AS "max_id" FROM "the_view" ...
1
vote
0answers
202 views

PostgreSQL Idle Session in Transactions with RELEASE_EXEC_SVP queries and lock

I am having a problem with having idle sessions in transactions. In pgAdmin Server Status, it is showing "RELEASE_EXEC_SVP_XXXXXX" (XXXXXX data are varied) as its query and it's locks also contain a ...
3
votes
1answer
1k views

How do you truncate a `date` (not `timestamptz`) to month?

Postgres has date_trunc which operates on timestamp or interval, and: Values of type date and time are cast automatically to timestamp or interval, respectively. In other words we can use ...
1
vote
2answers
2k views

How to calculate the cumulative difference between rows in Postgres?

I am in need of calculating the difference from the cumulative values for the previous day in Postgres based on session_id. Example as mentioned below: Date session_id upload_usage ...
2
votes
1answer
247 views

Is there a parser for Postgres' auto_explain to make it easier to read and understand?

We have a number of Postgres Functions that each call a number of other Postgres Functions. If you simply EXPLAIN ANALYSE a call like SELECT parent_function(), the output is minimal, since it doesn't ...
1
vote
1answer
324 views

How to remove object from json array?

My table: CREATE TABLE items ( id BIGINT PRIMARY KEY NOT NULL, name VARCHAR, images json ); images format: [ { "id": "owner", "full": "<url>", "thumb": ...
0
votes
1answer
84 views

Database storage limit for open source PostgresSQL

I just started using PostgresSQL. When I installed SQL Server Express years back, you typically had a database storage limit if you didn't install the full version. I think I remember it being 4GB, ...
0
votes
1answer
323 views

PostgreSQL - retrieving max primary key column to insert a new row - any alternatives?

We are using a software that needs to request the max primary key column value to add a new row into the table that was requested. I know that there are some alternatives like: Sequences, and GUIDs ...
3
votes
1answer
669 views

EXPLAIN ANALYZE shows no details for queries inside a plpgsql function

I am using a PL/pgSQL function in PostgreSQL 9.3 with several complex queries inside: create function f1() returns integer as $$ declare event tablename%ROWTYPE; .... .... begin FOR event IN ...
0
votes
0answers
103 views

Slow query on large table after new column is added

I have a table with more than 30 million records, and I recently added a new column with type text. ALTER TABLE database.b ADD COLUMN x TEXT; CREATE INDEX CONCURRENTLY idx_x on database.b(a, x); ...
2
votes
1answer
970 views

Connect to PostgreSQL via a ssh tunnel using Cygwin

I would like to connect to a Postgres server installed on a remote server using an ssh tunnel. I have Windows so I use cygwin for SSH. I do the following on cygwin: # Create the tunnel ssh -f ...
2
votes
3answers
3k views

Return total number of rows and selected (aggregated) data

I have a function which is selecting some data from a table. I would like to return the selected data and the total number of rows in that table. How can I do that or how can I get the same result in ...
1
vote
2answers
598 views

Function extract() from date returns double precision value

Why does the Postgres function extract() return double precision from date value? Only reason I could imagine is, there are some locales using calendars (those not Gregorian calendars and supported ...
0
votes
2answers
1k views

Logging in to phppgadmin remotely Ubuntu 14.04 Amazon EC2

I created an Ubuntu server instance on Amazon EC2 and installed php, postgreSQL, phppgadmin, and any other required features. After SSH'ing into the server using command prompt, I executed the ...
0
votes
0answers
38 views

Best practice for select or insert into [duplicate]

I was looking for quite some time on whats the best way of selecting or inserting some data. I have two tables: ReportData: report_data_id(pk), source_id(fk), value1; Source: source_id(pk), ...
0
votes
0answers
26 views

User with Connect privilege can create tables [duplicate]

postgres=# \list+ List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | ...
2
votes
1answer
286 views

WAL files are not copied

I'm using postgres 9.3 on Ubuntu 14.04 and I'm currently setting up a WAL backup. These are my changes to the postgresql.conf: [as described here: ...
2
votes
1answer
2k views

Using EXCEPTION to ignore duplicates during bulk inserts

In pq 9.3.5, I'm importing records from an external source where duplicates are VERY rare, but they do happen. Given a readings table with a unique compound key on (real_time_device_id, recorded_at), ...
0
votes
1answer
34 views

Query with column data for a max

In Postgres, I have a table like this: Column | Type | Modifiers ...
0
votes
0answers
59 views

Is it possible to create materialized view based on another materialized view in postgres?

I have a very large table and I summarize it in a materialized view. I then create an even smaller materialized view based on the earlier mview. The second mv is always showing zero rows. Is there a ...
35
votes
2answers
60k views

connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host

Hello I am trying to run a website sent to me but after doing so this error appeared connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host "4X.XXX.XX.XXX", user "userXXX", database ...
3
votes
2answers
3k views

PostgreSQL joining using JSONB

I have this SQL: CREATE TABLE test(id SERIAL PRIMARY KEY, data JSONB); INSERT INTO test(data) VALUES('{"parent":null,"children":[2,3]}'); INSERT INTO test(data) ...
1
vote
1answer
490 views

PostgeSQL 9.3: What is the difference between text and pg_catalog.text?

I have a classic 3-environments setting (dev, staging and production), each with its own RDS PostgreSQL 9.3. The client/server versions are psql (9.3.5, server 9.3.3). In order to keep the schemas ...
1
vote
1answer
1k views

PostgreSQL, UPDATE CASE statement uses huge amount of space

I am running a CASE statement on one of my tables and it consumes a huge amount of hard drive space that is never freed up even when i run VACUUM ANALYZE. Autovacuum is ON as well. I am using ...
1
vote
0answers
2k views

ERROR: relation “name” does not exist

I am trying to import a postgresql database table sent to me in phpPgAdmin, I am following the instruction from this import database But after doing so, several error appear, I also tried to import ...
4
votes
1answer
576 views

Get list of characters in column

I have a database table (UTF-8) with a lot of names in different languages with lots of special characters. In another script I use these names and one part is to replace the special characters. To ...
0
votes
1answer
301 views

Dump all binary columns from a table as files

I have a table in a postgresql database with a column of type bytea. I want to drop each binary entry as a file using the id as filename. What would a SQL query look like for this?
0
votes
2answers
4k views

Performance Benchmark - MySQL vs PostgreSQL [closed]

this is my first question here in DBA. I wish to do a performance comparison between MySQL and PostgreSQL using same database on the basis of total time taken to parse + execute + send result back ...
0
votes
1answer
211 views

Postgresql - Streaming replication - STONITH

Current situation: PostgreSQL, streaming replication Master failed, I created the trigger file. Slave is promoted to new master After the old master restarted successfully, I have to create base ...
2
votes
1answer
188 views

can I give permission to insert into view, but not to referred table?

Here I have a view in public schema referring to the table in private schema: CREATE OR REPLACE VIEW public.products AS SELECT products.id, products.product_id, products.title, products.company_id ...
0
votes
1answer
56 views

postgresql unable to login

I created a user named 'client', witch refuses to connect. Here's my client: CREATE ROLE client LOGIN ENCRYPTED PASSWORD 'md5d5bc32b75da65fe60067f501a4bb6665' NOSUPERUSER INHERIT NOCREATEDB ...
0
votes
1answer
104 views

Is content of the pg_autovacuum saved when pg_dumpall is used to backup the database?

From the docs: pg_dumpall also dumps global objects that are common to all databases. (pg_dump does not save these objects.) This currently includes information about database users and groups, ...
3
votes
0answers
161 views

Optimizing postgres row overlap constraints involving UUIDs and GIST

For various reasons all my tables have UUIDs as primary keys and I need to exclude duplicate rows where the combination of area_id, user_id, and finished_at <-> started_at window. The only way I ...
3
votes
1answer
155 views

PostgreSQL CREATE INDEX memory requirement

I've got 6GB of RAM on my laptop and for a test I'm creating a 50mil-rows table in PostgreSQL 9.3. I then want to create an index on the table. The table and the resulting index together (or twice ...
2
votes
0answers
464 views

UPDATE/DELETE performance on partitioned table

Hi I'm using table partitioning in my Postgres 9.3. I've notice that the DELETE and UPDATE queries are much slower when I'm doing them via the parent table. I see that the constraint_exclusion is ...
0
votes
1answer
46 views

Email open rate calculations in subqueries with postgres

So I'm relatively new to PostgreSQL, but here we go: I have a pgsql database that is receiving HTTP Post Email Events from my Sendgrid email server, and am trying to make a dashboard that displays ...
0
votes
1answer
71 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
150 views

Reassigning surrogate keys?

I have a few tables with surrogate keys (SERIAL PRIMARY KEYs unique to each tables). These tables are used by other tables, with references to these keys. My usage of these is quite simple : select ...
4
votes
1answer
385 views

How to determine cost/benefit of adding an index?

According to Craig Ringer: While it's usually a good idea to create an index on (or including) your referencing-side foreign key columns, it isn't required. Each index you add slows DML operations ...
3
votes
1answer
955 views

Removing a FAILED node from repmgr cluster

How do I remove failed node from a PostgreSQL cluster? postgres@db-prod-ms47ox-10-3-104-92:~$ repmgr -f /etc/repmgr/repmgr.conf cluster show Role | Connection String * master | ...
4
votes
2answers
910 views

How long to learn PostgreSQL?

I want to learn PostgreSQL and How to make the learning process efficient, but really don't know how long it would take even after googling my question. I used MySQL before and knew relational ...
0
votes
1answer
390 views

how are passwords encoded in pg_shadow

How are passwords encoded in column passwd of pg_shadow? Doing a: select passwd from pg_shadow; I see values like the following: md52c92c858aa1fa12144f1dcbd4ca9c3a0 ... which, doesn't seem to ...
0
votes
1answer
490 views

Prevent Primary Key increment when an INSERT statement fail in PostgreSQL

I have a simple table in my PostgreSQL database like this: CREATE TABLE person_type ( id serial NOT NULL, name character(55) NOT NULL, CONSTRAINT person_type_pkey PRIMARY KEY (id), CONSTRAINT ...
0
votes
1answer
230 views

PostgreSQL Role Configuration

I am using PostgreSQL 9.3. I am working with a friend of mine on a project on which both of us would need access to the same database and all of the permissions on every table. So I created a Role ...
0
votes
1answer
257 views

Database Normal Form (financial statement)

I would like some advice on how to design the database for financial statement data which objectively addresses the tradeoffs between usability, performance, maintenance and adherence to normal form. ...
1
vote
1answer
917 views

postgres materialized views and indexes to improve aggregate performance

I have a large table with a number of materialized views used for pre-calculating aggregates. New data is inserted only once a day so think of this as a data warehouse type system. Obviously when I ...
1
vote
2answers
93 views

Remove duplicates and connect relations to the first one

Guess I can use some help over here. I have split a products table into two tables, products and prices. This is the original table: products: +----+--------+-----------+-----------+-----------+ | ...