PostgreSQL is an enterprise class, open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.

learn more… | top users | synonyms (1)

1
vote
1answer
45 views

pg_restore: [archiver] did not find magic string in file header

I'm using PostgreSQL 9.1 and want to restore backup files generated with pg_dump: sudo pg_dump -h 127.0.0.1 -U postgres --clean --inserts -E UTF8 -f out.sql database_name This command generates a ...
0
votes
1answer
39 views

Postgresql pg_basebackup from slave didnt save WAL's

Good day please tell me whether it is possible to shoot a backup with utility pg_basebackup from the slave server (where of streaming replication with the help of this utility pg_basebackup) I have ...
1
vote
1answer
24 views

Calculate Difference in Overlapping Time in PostgreSQL / SSRS

I'm using PostgreSQL 9.2 and SSRS 3.0. I'm trying to calculate the difference in non-overlapping time entries in a timesheet. My data looks like this: I would like this to return: I can get ...
2
votes
1answer
41 views

Why does PostgreSQL require a database connection?

A more precise version of my question might be, does the fact that PostgreSQL requires a database connection (as opposed to some sort of generic database cluster connection) follow directly from some ...
4
votes
1answer
54 views

Compacting a sequence in PostgreSQL

I have a id serial PRIMARY KEY column in a PostgreSQL table. Many ids are missing because I have deleted the corresponding row. Now I want to "compact" the table by restarting the sequence and ...
1
vote
1answer
15 views

Are toast columns compressed also in shared_buffers?

Reading here I cannot find a clear answer: http://www.postgresql.org/docs/9.1/static/storage-toast.html I need to know if setting storage to EXTERNAL for a particular column, I can gain a little ...
0
votes
1answer
22 views

Returning rows based on time of day respective to their timezone [closed]

I also posted this on Stack Overflow. I got no answers there so I'm hoping someone here might be able to help. I have a table in which every row represents a user, I am also storing the user's ...
1
vote
1answer
25 views

Create one row for every user in a Postgres table

I have a bunch of rows of users in a users table, for example: | id | name | |----|-------| | 1 | Chris | | 2 | Max | | 3 | Steve | For each one of these users, I'd like to create a row in ...
4
votes
1answer
115 views

How to display Postgres version in the CLI pompt?

I whish to display the version of the server I am connected to in the CLI's prompt. From what I read in the documentation, it is possible to execute a shell command, it is possible to display a psql ...
1
vote
0answers
20 views

Do you need external caching for a Postgres database?

I am currently building a server and have just written a database connection using JDBC, Jooq, and added a DAO layer, which also handles transaction management. I remember that databases can cache ...
1
vote
0answers
20 views

Postgres with no clients still chatty on network

This may be a non-issue but I was doing some network monitoring on my Mac and noticed that PostgreSQL (9.4.4) was still sending and receiving packets even when I'm not aware of any clients (localhost ...
0
votes
0answers
31 views

How to make a query from a database with schemas to a database without schemas, and vice versa, with DBLINK in postgres

I have a DB (DB1) in postgres 9.4 who use DBLINK to query another DB (DB2), and vice versa, using views and functions. But now i have to add 2 schemes to DB1 (CLIENT1 And CLIENT2) and i want when i ...
1
vote
0answers
27 views

Optimize PostgreSQL server setting for extremely wide tables

We do have a dedicated server where only PostgreSQL 9.4.4 server is running, 2 physical cores, 16 logical cores, 128GB RAM. On this server we have quite atypical data - there are several tables with ...
1
vote
0answers
29 views

Query planner slow to use newly created index on database under heavy load

We have some heavily loaded databases with more than 1k requests / sec and have started to have problem with index maintenance. Sometimes we need to update/recreate one of the indexes we have, for ...
0
votes
0answers
10 views

Get all the numbers between two numbers [migrated]

I want to display all the numbers (even/odd/mixed) between two numbers (1-9; 2-10; 11-20) in one (or two) column: Example initial data: | rang | | r1 | r2 | --------- ...
2
votes
2answers
39 views

What's the quickest way to periodically export PostgreSQL data to a local file on Windows?

I have access to a PostgreSQL database that I need to get data from for another system. The other system is running Windows 7 and does not (yet?) have PostgreSQL installed. Ideally, I'd like to have ...
0
votes
1answer
58 views

How does Postgres make its B-tree index?

I want to estimate how many reads the Partial index of PostgreSQL with the method B-tree requires, since I have not been able directly to alter the block size. PostgreSQL manual about the index here ...
2
votes
2answers
31 views

postgresql trigram search gets much slower as search string gets longer

In a postgresql-9.1 database, I have a table table1 with ~1.5M rows and a colum label that is trigram-indexed (actually, the trgm is on lower(unaccent(label)) (unaccent is made immutable for the index ...
4
votes
1answer
27 views

Dynamic casting from text within plpgsql

Many tables in my database share a common layout, in particular they have a serial primary key named after the relation name. Updating these tables from within a web application often involves a query ...
0
votes
0answers
45 views

Indexes are not created exactly as like mysql

I have connected ofbiz with postgres. It will create tables automatically. But when i compare mysql and postgres tables, i can see that some indexes are missing in postgres. If i migrate it from mysql ...
1
vote
2answers
15 views

Amazon RDS Postgresql adding new extensions

I want to download jsonbx to RDS instance. RDS PostgreSQL Features Supported shows only built-in features. Does this mean there is no way we can install an extension to RDS which is not in the Feature ...
4
votes
1answer
184 views

How to model complex systems like networks in Postgres

I need help with modelling of a network involving switches (24 port, 48 port, POE, and a combination of those) and edge devices (computers, IP cameras, telephones). From an object oriented ...
0
votes
1answer
11 views

postGIS schema qualifier - best practice

There are a number of cases where the EXTENSION for postGIS is created iwth a schema qualifier. CREATE SCHEMA postgis; CREATE EXTENSION PostGIS WITH SCHEMA postgis; why is that? what are the ...
3
votes
1answer
59 views

Step through multiple arrays in parallel with FOREACH

Is it possible to loop over multiple array with FOREACH in PL/pgSQL? In my case, 3 arrays with identical dimensions. How can I pass the elements like: for(int i = 0 ; i < array1.length ; i++){ ...
1
vote
0answers
17 views

Pass additional data in connection for use by PGSQL Trigger

I would like to add some auditing triggers to PG, to log all changes to tables. I have found an example trigger ( wiki.postgresql.com - 9.1+ audit trigger ), which gets me started. What I would ...
2
votes
1answer
32 views

Extend a single week's win/loss ratio calculation to a whole season

I'm quite inexperienced at SQL, and I'm trying to improve a query in a small project. The database schema looks somewhat like this. (I've reduced to relevant information, if there seems to be ...
4
votes
1answer
45 views

How to deduplicate SELECT statements in relational division?

I have a query that does a lot of duplicate work: SELECT visitor_id, '1'::text AS filter FROM events WHERE id IN (SELECT event_id FROM params WHERE key = 'utm_campaign' AND value ...
1
vote
1answer
23 views

How to merge partitions in Postgres?

I have a table that is partitioned by date on a monthly basis beginning January 1, 2014. There is significantly more rows of data in 2015 than there were in 2014 and the data in 2014 is not accessed ...
1
vote
0answers
42 views

Splitting logging into a separate database

In a postgresql database for a asp.net mvc website I have 3 tables (visits, uservisits, hits) that have update/insert query's running on them on every pageview. Rows in visits (for all visitors) and ...
2
votes
0answers
34 views

How to implement search for world-wide Wikipedia/OSM places with alternative-names

I would like to implement a search-backend which returns a location-aware search results of all Wikipedia / OSM place names, while recognising place names in all languages. Thus, if you search for ...
2
votes
1answer
88 views

PostgreSQL doesn't use all the RAM it could

I'm having performance issues with my PostgreSQL 9.4.1 server. I have tuned the server using the usual best practices (pgtune + google). Here's the relevant config: # <snip> the default config ...
2
votes
1answer
64 views

How can filesystem storage (for blobs, images, etc.) be integrated with (traditional) database storage (e.g. MySQL/Postgres)?

If I use my database for relational data and opt to store large binary blobs in the filesystem, how can I integrate the two? How does this process work? I know that the database will need to store a ...
1
vote
1answer
39 views

PostgreSQL “size of temporary files”

I've imported data into a new database (about 600m rows of timestamp, integer, double). I then created some indexes and tried to alter some columns (got some out of space issues), the database is ...
4
votes
1answer
60 views

Exactly what error is returned by PostgreSQL `FOR UPDATE NOWAIT` when a selected row cannot be locked immediately?

The PostgreSQL 9.4 documentation states that adding the NOWAIT option to a SELECT FOR UPDATE means an error is generated when a row cannot be locked: To prevent the operation from waiting for ...
3
votes
1answer
37 views

PostgreSQL: Run EXPLAIN on all queries in pg_stat_activity

I am investigating high load on a PostgreSQL database on RDS. As a part of the investigation, I'm running the following query: SELECT now()-query_start, state, query FROM pg_stat_activity ORDER BY 1 ...
1
vote
1answer
26 views

Postgres data integration

Consider a scenario where I have 3 standalone running Postgres engines that are installed on three separate machines. The data on this machine is inserted, updated or removed each month by employees. ...
2
votes
1answer
30 views

Adding new null column to table caused lock

Yesterday we ran a schema migration on our production database that we thought would be safe, but ran into an issue. It seems as though adding the new column caused a lock and our application could ...
0
votes
1answer
14 views

Any gotchas with changing IP address of Postgres Master?

We have to replace our Master Postgres database and due to restrictions in AWS, we cannot use the same IP address for the new server (not 100% true, but cannot do this until the original is terminated ...
1
vote
1answer
37 views

postgresql join - too long when no results found

I have two tables: controller_monitor and controller_monitor_reading. controller_monitor Column | Type | Modifiers ...
4
votes
1answer
77 views

Index is not used with table inheritance

I have a PostgreSQL 9.0.12 database with a master table and 2 child tables. My tables: CREATE TABLE test2 ( id serial PRIMARY KEY, coll character varying(15), ts timestamp without time ...
0
votes
1answer
26 views

How to get postgres to resume processes after it hangs when the RAID drive hosting the tablespace times out

Nearly once per day I'm getting a timeout that shows up in console like: 7:1:36 PM kernel: Areca RAID volume scsi command timeout (target=0 lun=0) which corresponds exactly to when all postgres ...
0
votes
1answer
15 views

PostgreSQL: Find rows for which any index of any value contained is over a given size

I have a database originally built with an instance of PostgreSQL with a custom page size much larger than the default. I'm trying to migrate its contents to a standard, 8k-page database. Because ...
1
vote
1answer
26 views

Comparing the data in two Postgres databases

I am looking for a way to compare the data in two Postgres databases. Basically a 'Before' and 'After' snapshot of a transaction being posted. What I am looking for is all the tables/records that ...
1
vote
0answers
18 views

Use pg_dump with postgis extensions?

I'm using Postgres 9.4. I'm trying to dump a database (both the schema and the actual data) with the PostGIS extensions (set up using CREATE EXTENSION): $ pg_dump prescribing -U prescribing -h ...
0
votes
0answers
17 views

no pg_hba.conf entry for host “10.1.20.46”, user “redruby”, database “production”, SSL off

Two days ago I was able to ssh onto an utility server and perform some direct work on a production database. Yesterday, I received the error above in the title. What reason could this error have ...
4
votes
1answer
60 views

Managing and speeding up queries on PostgreSQL table with over 3 trillion rows

I have time series data which spans over 10 years and has over 3 trillion rows and 10 columns. At the moment I use a PCIe SSD with 128GB of RAM and I am finding that querying takes a significant ...
3
votes
1answer
67 views

Performance issues with inherited tables and indices

I have a PostgreSQL database with a master table and 2 child tables. My master table: CREATE TABLE test ( id serial PRIMARY KEY, date timestamp without time zone ); CREATE INDEX ON ...
0
votes
1answer
18 views

PgTap, unit test a foreign key is deferrable

I am using PgTap to unit test a Postgres (v9.4) database structure in a continuous migration process. It works great. Here is my question: « Is there a way to control that a foreign key constraint is ...
2
votes
2answers
253 views

Numeric vs Integer for a column - size and performance

I have an application which uses a PostgreSQL table. The table is very big (billions of rows) and has a column which is an integer. The integer can be up to 6 digits, i.e. 0-999,999 , no negatives. ...
9
votes
1answer
36 views

When are privileges listed in \l and when not?

When are access privileges listed by \l, and when are they not? The access privileges listed by \l can change after a grant and revoke: $ createuser -EP my_readonly $ psql development development=# ...