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

How to fix postgres stuck in recovery mode?

I have Jira and Confluence installed in a server. We didn't have any free disk space one day, and these services didn't started correctly, so after deleting some files we now have some free space ...
6
votes
1answer
350 views

What happens when we add an index to an existing table with a large amount of data?

I've a table which will contain around ~15 Million records. Now I need to add an index to the table. Adding an index will take some time to update every entry in the table. I'm quite confused ...
5
votes
0answers
144 views

To partition or not to partition?

Having already read several questions on SO, external blog posts and manual SO: Foreign Key constraint to partitioned table in Pg dba.SE: Different ways of handling FK to partitioned table in Pg ...
2
votes
2answers
90 views

Filesystem tuning for PostgreSQL

I want to install PostgreSQL 9.x version in an Ubuntu 14.04 workstation and I'm looking for a recommendation on how to set it up in order to get a good read-write (50/50-balance) performance keep a ...
4
votes
1answer
67 views

Improving sort performance in GROUP BY clause

I have two tables in Postgres 9.4.1 events and event_refs with the following schemas: events table CREATE TABLE events ( id serial NOT NULL PRIMARY KEY, event_type text NOT NULL, event_path ...
3
votes
1answer
27 views

Postgres - estimate end time for a long update

I have currently running a long update (~million rows based on a postgis st_within condition) that was expected to finish in an hour or so based on similar previous queries. However, it's been three ...
2
votes
1answer
30 views

Basic transactional DDL script in PostgreSQL

PostgreSQL newbie here trying to put together a transactional DDL script to atomically create a database and its schema in Postgres 9.3: in a transaction, create a few tables; if any errors, roll the ...
0
votes
1answer
42 views

How to return a set of rows from this function?

I am new to Postgres functions and have a table with the following structure: CREATE TABLE options.options ( delta double precision, gamma double precision, rho double precision, theta double ...
0
votes
0answers
22 views

Postgres 9.2 replica can't catchup to master

I have configured a master/replica setup for postgres 9.2 on Amazon Linux. PS output shows that the replica is connected and streaming, but also recovering. This has been going on for about 24 hours. ...
0
votes
0answers
16 views

Where to put snowball files to add a snowball stemmer to PostgreSQL database?

I've downloaded Czech snowball stemmer source files (.c, .h files) and compiled them into .o files and I know the correct SQL syntax for adding the dictionary (it's in the docs). I keep getting the ...
5
votes
1answer
96 views

Index not being used in SELECT query

I have a table of about 3.25M rows with the follow format in Postgres 9.4.1 CREATE TABLE stats ( id serial NOT NULL, type character varying(255) NOT NULL, "references" jsonb NOT NULL, ...
1
vote
2answers
69 views

Postgresql text column character Limit

Im not even close to a DBA, so plain English please. I recently needed to look into a performance issue on a relatively small PG database, but yet is performing very slow. the server is running PGSql ...
0
votes
1answer
30 views

postgresql.conf file found in both /usr/share/postgresql and /etc/postgresql/

While troubleshooting another issue I noticed that in my Ubuntu 14.04 system there are two postgresql directories containing a postgresql.conf file: /usr/share/postgresql /etc/postgresql/9.3/main/ ...
4
votes
1answer
41 views

Group and count array elements using intarray

I am working on a Postgres 9.4 project with the intarray extension enabled. We have a table that looks like this: items ------------------------------------- id name tag_ids ...
1
vote
1answer
37 views

Migrate Postgres from VM to VM

I have 2 VMs, both are identical in structural terms (came from the same snapshot, nothing installed, upgraded or removed). One VM is "clean", never used. The other VMs was heavily used and it has ...
4
votes
2answers
90 views

Optimize a query with small LIMIT, predicate on one column and order by another

I'm using Postgres 9.3.4 and I have 4 queries that have very similar inputs but have vastly different response times: Query #1 EXPLAIN ANALYZE SELECT posts.* FROM posts WHERE posts.source_id IN ...
3
votes
0answers
85 views

Postgresql Strange Characters

I've found a lot of strange characters on data rows like this ÃÃÂà The data inserted by perl script which converting to UTF8 ...
1
vote
1answer
233 views

Upgrade PostgreSQL 9.4 to 9.5 : LC_ALL unset

I'm trying to upgrade my PostgreSQL 9.4 database to PostgreSQL 9.5 but everytime while running sudo pg_upgradecluster 9.4 main I get this error: Stopping old cluster... Disabling connections to the ...
3
votes
1answer
39 views

How to get 0 as array_length() result when there are no elements

I have a query like this: select id, array_length(users_who_like_ids,1) as ct from queryables order by 2 desc; But empty arrays (with no elements) sort on top. I'd rather have array_length() return ...
0
votes
1answer
11 views

How Can I Access the postgres database in a Script Executing pg_dumpall?

I want to run the following script using my role which includes the postgres database. pg_dumpall -c -f ~/Desktop/pgdumpall_proddb_`date +%Y%m%d_%H%M%S`.sql Here are the settings in pg_hba.conf: # ...
3
votes
1answer
48 views

PostgreSQL translate special characters?

Description: PostgreSQL 9.3 String: 'ì ằ ú ề' Desired Result: 'i a u e' My code: select translate ('ì ằ ú ề', 'ìằúề', 'iaue') ; -- it works. Result: i a u e Question: If I use it this way, I ...
1
vote
0answers
10 views

how to clear the postgrsql-9.1-main-log

Is there an easy way to clear the logfile of postgresql? the logfile is 6044 MB, can you use cp /dev/null file-name or do you mess up the database?
1
vote
0answers
14 views

remote postgres user logon with certificate

I would like to have each remote user logon using an SSL certificate and use their postgres logon role to better track their use of the system. I can set up a certificate for a given user per the ...
1
vote
0answers
29 views

postgresql columns are inside quotes - “ ” and uppercase but occurs relation does not exist error when queried in java

I'm using Java and Postgresql and I've created a relation "UserTable", and when I open PgAdmin, this is how I see my database. But when I query the UserTable, in Spring Tool Suite using Java, I get ...
0
votes
1answer
44 views

Postgres datetime sort slow and not using index

Using Postgres 9.4, I'm trying to order a query by an indexed datetime column. explain SELECT g.*, array_agg(gp.platform_id) as platform_list, array_agg(gm.metacritic_id) as ...
3
votes
1answer
39 views

Join table into different columns

I have two tables (simplified). table1: - id (serial) table2: - table1_id (integer) - value (text) - type (integer) I want to join these two tables, but depending on the type, I want ...
0
votes
2answers
60 views

Perform join on multiple tables

I have three tables right now in a postgres DB: Artists ID Name Shows ID Date Venue Price Is_sold Ages Time Multi_day Pit Artists_shows ID Show_id ...
1
vote
0answers
27 views

How to make a PostgreSQL trigger thread-safe

I want to make my PostgreSQL trigger function do something like incrementing counters etc (something complex with multiple queries etc) thread-safely (so the same function triggered concurrently won't ...
3
votes
1answer
85 views

Why am I getting old data?

Today I discovered something weird on PostgreSQL 9.5. (I have no idea whether this is because of beta or not.) When I want to fetch data, I get old and deleted data from query. I then do VACUUM FULL ...
2
votes
1answer
59 views

Postgres 9.4 won't accept remote connections

I'm running postgres 9.4 on Ubuntu server 14 which is running on virtualbox. After a reboot no remote connections would work to postgres. I have other services on server that still take remote ...
3
votes
1answer
53 views

Indices for a join query with datetime range

Given the following query: SELECT DISTINCT COUNT(DISTINCT "patients"."id") FROM "patients" INNER JOIN "transactions" ON "transactions"."patient_data_id" = "patients"."patient_data_id" WHERE ...
2
votes
0answers
17 views

Promote Postgres standby while materialized view updates in primary

I have one primary Postgres server (serverA) and one standby Postgres server (serverB), both running Postgres 9.4 with Streaming Replication enabled. Is it possible to update a materialized view on ...
1
vote
0answers
28 views

PostgreSQL long transaction on a table performance

We have a long processing pipeline running on PostgreSQL 9.0. Is it faster or slower to perform many operations on a single table within one transaction, versus doing auto-commit for each operation? ...
0
votes
1answer
35 views

If else with 5 possible cases PostgreSQL

I have bunch of records in Database and i have been searching for query on NULL json object and i got that right,but now its difficult for me to continue it on 5 possible cases For example: am ...
4
votes
1answer
41 views

Fastest way to find (rare) rows with a null value among millions of rows?

If I have an addresses table with ~ 4 million rows in a Postgres database. Is there a way to optimize the following query so that I can find the few rows where address_id is NULL? Sometimes I need to ...
1
vote
0answers
26 views

Design options for time series scientific data

I've been scratching my head over the last few days trying to find a solution that best suit my needs. I tried to think that what I want is probably not very different from what many others wanted ...
0
votes
0answers
23 views

How autocommit = 1 impacts statements between BEGIN/COMMIT?

Let's say I have following code: begin; set autocommit = 1; update tbl1 set col1 = 1; update tbl1 set col1 = 2; commit; What would be the difference if autocommit (line 2) was set to 0?
0
votes
1answer
56 views

PostgreSQL force sslmode=verify-full

I have created my CA, Server and Clients certificates and keys. I have tried connections using sslmode flags with psql. I have edited the pg_hba.conf file to force SSL using hostssl only. Now I would ...
-1
votes
2answers
91 views

How to delete millions of records from table

I am using postgree 9.4 database and I have a table snapshots with 700+ million rows and I want to delete about 9 million rows. I have tried different queries to delete records, but no success. Query ...
3
votes
1answer
32 views

Can atomic counter in trigger lead to “could not serialize access due to concurrent update”?

I have the following code: create table users (id serial primary key, comments_counter integer default 0); create table comments (id serial primary key, user_id integer references users(id) not null) ...
3
votes
3answers
85 views

SUM of ever increasing column

I have a table of "transactions" where each transaction has an amount: http://sqlfiddle.com/#!15/42849/1 Records in the table are never REMOVE'ed or UPDATE'ed. Only new transactions are added. I ...
0
votes
0answers
47 views

Migrate json file to Postgres database

Is there any easy way to migrate json file to Postgres database? I googled, but it seems to me that there in no psql command to load json data, like there is for example \copy for csv.
0
votes
1answer
18 views

Check lock count for PostgreSQL

Up to now we used check_postgres.pl to monitor our database. We used this to check the count of the locks: https://bucardo.org/check_postgres/check_postgres.pl.html#locks We often see more than 150 ...
1
vote
0answers
32 views

Create a trigger in Postgres that will insert a record to another table with added column

I have these 2 tables created in postgres, CREATE TABLE EMPLOYEE( ID INT PRIMARY KEY NOT NULL, NAME VARCHAR(100) NOT NULL, ADDRESS VARCHAR (250) NOT ...
1
vote
0answers
8 views

xmlforest() omits any fields that are null entirely, instead of creating an “empty” element

I'm know I can use an empty string, but I don't want to lose the null functionality in the database. In some cases null is appropriate and I can't use empty strings. Other than a empty string, how ...
3
votes
2answers
37 views

Restore PostgreSQL database (or db names) to new version, from files?

Scenario in short: a development laptop broke old HDD is still readable new laptop has PostgreSQL 9.4 instead of 9.1 both laptops use a flavor of Ubuntu Linux Question 1: Is my understanding ...
4
votes
1answer
68 views

I Need Help Setting Up Roles and Passwords in PostgreSQL

I'm using the postgres.app on my Mac Mini Server running version 9.4.0 using El Capitan. I have not done any customization in pg-hba.conf. I do not have a .pgpass file. I believe that .pgpass is used ...
1
vote
0answers
24 views

Slony with Amazon's RDS service?

Is it actually possible to somehow use Slony with Amazon’s RDS service? I’m aware that there’s no such thing as a host that you can log into to make Slony’s configurations, but perhaps it can be done ...
0
votes
1answer
24 views

PostgreSQL - duplicate data show and count

This is table. CREATE TABLE cameras ( id integer NOT NULL DEFAULT nextval('sq_streams'::regclass), created_at timestamp with time zone NOT NULL DEFAULT now(), updated_at timestamp with time ...
3
votes
1answer
103 views

pg_stat_statements not found even with "shared_preload_libraries = 'pg_stat_statements'

I have followed the instructions given below: http://www.postgresql.org/docs/9.3/static/pgstatstatements.html ... to the effect of adding the following lines: # postgresql.conf ...