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)

2
votes
0answers
49 views

ST_GeomFromGeoJSON() Causes postgres to crash

I'm running into a strange problem which I can't solve. The server crashes when executing some postgis related queries. After some debugging, using the examples provided by postgis, it appears the ...
3
votes
2answers
29 views

Do Postgres GIN indexes need to be rebuilt periodically?

Do they get bloated like btree indexes? If so how can the bloat be measured e.g. by an automated reindexing script. I am interested in improving query performance and saving space.
2
votes
1answer
33 views

SQL Statements truncated in log files

Does PostgreSQL cut statements if there is a subselect in it? We use log_min_duration_statement = 20ms to find statements which need a long time. But unfortunately the statements are truncated: ...
1
vote
1answer
38 views

How to design tables for airbnb style booking system

all. My English is not very good, but I will try to describe my question as clear as possible. I want to design a airbnb(https://www.airbnb.com) style booking system, this kind of booking system ...
2
votes
1answer
42 views

Marking duplicate records

I have a list of things, some of which are duplicates. create table things ( id int primary key, title text not null, author text not null, duplicate_of int null references things(id) ); ...
1
vote
1answer
50 views

How to restrict access to current user record and its rights in postgres

Postgres 9 database contains additional information about postgres users: create table public.user ( user char(10) primary key, -- CURRENT_USER in postgres name char(30) ); and user roles; ...
0
votes
1answer
23 views

What determines if a bytea has 1 or 4 extra bytes?

When inserting a BYTEA string in PostgreSQL the documentation says that such a string will occupy 1 or 4 bytes plus byte string length. The question that I can't seem to find the answer to is what ...
1
vote
1answer
74 views

How many rows will be locked by SELECT … ORDER BY xxx LIMIT 1 FOR UPDATE?

As was asked on stackoverflow but then for MySQL, I was wondering how this works in PostgreSQL. How many rows get locked when I do a "FOR UPDATE" in combination with a "LIMIT", a "ORDER BY" and some ...
0
votes
1answer
12 views

benefit of using [md5] over [trust] authentication for local Unix sockets

I am following a PostgreSQL installation guide that advises (subsequent to installing the database for the first time), to setup a password for the postgres user: alter role postgres password ...
1
vote
1answer
30 views

remove password requirement for user postgres

I understand that, upon installation, PostgreSQL has no password for its db root user (postgres): postgres=# select usename, passwd is null from pg_shadow; usename | ?column? ----------+---------- ...
0
votes
0answers
26 views

Error importing postgres DB dump into empty database

I need to import my psql dump into my fresh psql database. When I execute the following command, I get errors. psql -U user new_database < filename.sql Error I got: ERROR: out of memory DETAIL: ...
1
vote
1answer
33 views

Copying database to different hard disk

Total noob at database administration here, excuse my ignorance. I have a server running a Rails application that's using PostgreSQL. The database is saved on drive /z/, which seems to have filled ...
0
votes
1answer
34 views

How to create a tablespace on Windows 7 for Postgresql 9.2?

I try to run this simple command: CREATE TABLESPACE tb_rec OWNER postgres LOCATION 'd:/tablespaces'; However, the tablespace is not created and I got this message: ERREUR: n'a pas pu ...
0
votes
1answer
27 views

pgbench-tools failed authentication

I'm trying to configure pgbench-tools and went to run it per the README and got this error -bash-4.2$ ./newset 'Initial Config' psql: FATAL: Ident authentication failed for user "postgres" psql: ...
1
vote
1answer
34 views

Why “SET LOCAL statement_timeout” does not work as expected with PostgreSQL functions?

My understanding is that PostgreSQL functions are executed similar to a transaction. However, when I tried to "SET LOCAL statement_timeout" within a function, it did not work. Here's how it works ...
0
votes
2answers
30 views

Using pg_dump and psql to transfer table with timestamp into another Db

I am using the following pg_dump and psql command lines through the terminal in order to transfer my table from one database to another. pg_dump -U postgres -t public.table1 database1 | psql -U ...
1
vote
1answer
33 views

How can prevent writes to a Postgres schema for a period of time?

I am moving a schema from one server to another using pg_dump + pg_restore + drop schema cascade. I need to know that no applications are writing to it (or at least that the writes will fail) during ...
0
votes
1answer
49 views

After upgrade from 9.1 to 9.3, commands still point to 9.1

Upgraded from 9.1 to 9.3 on my Ubuntu 12.04 server. Everything worked great but running commands like 'createdb' still runs them from the 9.1 path. /usr/lib/postgresql/9.1/bin/createdb: unrecognized ...
0
votes
1answer
52 views

copy data from public schema to another schema in postgresql

I'm creating a multi-tenant RoR app using PostgreSQL 9.3.5 running on linux (Ubuntu 14). I'd like to copy the data from my public schema to another schema. I tried: pg_dump -U my_username -h ...
1
vote
1answer
28 views

Change constraint on column based on value of another

Is it possible to change a constraint on a column in postgres based on the value of another column? E.g. (pseudocode): CREATE TABLE transactions( id SERIAL PRIMARY KEY, type TXN_TYPE NOT ...
0
votes
0answers
36 views

PostgreSQL queries slower than before?

I am using PostgreSQL 9.3 server on my production server. Everything had been working fine for the past couple of months. But since the past week or so, PostgreSQL seems to have becomes very slow with ...
0
votes
0answers
43 views

pgAgent Job's “last result” is continuously “running”

I am using PostgreSQL-9.3 on Ubuntu-14.04 and using "/usr/share/pgadmin3/pgagent.sql" I have configured my pgAdmin3 to use pgAgent. postgres is my maintenance Db and I have run the pgagent.sql content ...
1
vote
0answers
47 views

Multiple databases with medium tables or Single database with huge tables

I've developed a shop management software using Postgresql and JAVA. I would like to know which of the solutions below is the best one for my specific situation: Only one database with huge tables ...
2
votes
0answers
46 views

Cannot drop a Postgres table

I want to drop a few tables in my postgresql database. I am able to drop a few tables but there are 3 tables which I am unable to drop. I need to drop a few tables in my Postgres DB. I tried the ...
0
votes
0answers
28 views

Calculate Quantity On Hand By Location

How can I calculate quantity on hand by location and validate the proposed location quantities against inventory transaction? Currently, I do not track location. I calculate total QoH via the ...
0
votes
1answer
39 views

PostgreSQL import XML to empty database, automatically create columns

The server is Linux but the development machine is Windows so I'm using psql command line. I tried \i C:/Users/John/Desktop/Parts.xml which did not work so either \i doesn't work with XML or I'm ...
0
votes
0answers
33 views

Looking for a recipe to merge multiple rows to single row

I have a aggregation table that is written by multiple processors (around 20). Each row has a composite key - location-id, campaign-id,... and aggregate stats about it. I need to merge the ...
1
vote
1answer
37 views

Will switching from Concrete Table Inheritance to Single Table Inheritance improve the performance of my queries?

I currently have an application that allows people to view and share 10 different types of posts (ex SportsPost, WorldNewsPost, TechnologyPost, etc). All posts types are conceptually the same, but ...
4
votes
2answers
344 views

How to understand the PostgreSQL discrepancy between connection pool recommendation ((2 * n_cores) + n_disks) and support for 100s of connections?

From PostgreSQL docs: Number of Database Connections -> How to Find the Optimal Database Connection Pool Size for optimal throughput the number of active connections should be somewhere near ...
1
vote
2answers
86 views

Need help designing a database

We are designing a system which holds statistical parameters about a particular user. As the users use the system the statistics change and the updated statistics are stored in the database. So for a ...
1
vote
1answer
40 views

How to manipulate join order of PostgreSQL optimizer?

I am working on table join order optimization. While query processing, I want to get table join order generated by optimizer, and update it by using my own algorithm in PostgreSQL? How can I update ...
0
votes
0answers
35 views

How to enable implicit casts in PostgreSQL >=9.2?

Coming from this topic on SO a user suggest me to enable implicit casts in my PostgreSQL and I don't know how to do that. I also don't know if this is a good practice or if it's the solution to the ...
0
votes
1answer
28 views

Drop/Create database and create schemas all in the same script at PostgreSQL it's possible?

I'm working on a project where all the time I need to repeat this tasks: Drop database sis_db Create database sis_db Create database schemas by running a custom SQL file which contains this code: ...
0
votes
3answers
72 views

Get latest status of an item in the database

I have this table: ID|Item-FK |timestamp|status ============================= 1 | 123 | ... | OK 2 | 123 | ... | FAILED ... I want to query get all items which last status was FAILED. ...
1
vote
1answer
49 views

EXECUTE within function not using index?

I have an accounts table with ~200k rows and an index for these columns: account_type_id BIGINT , member_id BIGINT , external_id VARCHAR(64) CREATE INDEX account_full_qualifiers_idx ON ...
0
votes
1answer
34 views

Database Security - Prevent column writing?

I am using a Postgres db. I am concerned if someone gets in my DB, they could change the price of my products to $0 and download my products. Am I able to prevent writing / editing of that column ...
0
votes
1answer
45 views

Postgresql doesn't accept replication connection

Plain old streaming replication. PostgreSQL: 9.2.7 Windows 8.1 64 bit My primary and secondary clusters are on same windows machine. I have already done pg_start_backup() and everything, so both ...
0
votes
1answer
32 views

Does PostgreSQL have a variable character storage layer that optimizes storage space by automatic dedupe?

My application has a Product and a Purchased table. The Purchased table includes a full copy of the Product row at time of purchase, so that, even if a product name or description was changed, the ...
2
votes
0answers
39 views

How can I avoid breaking Postgres sync replication when building a large index?

We have pairs of Postgres 9.2 servers using synchronous replication. Sometimes rebuilding an index on a large table will break replication due to WAL segments not being kept around long enough. We are ...
0
votes
0answers
13 views

missing pgpool-walrecrunning.so in PostgreSQL-9.4-pgpool2

I am new to PostgreSQL and pgpool concept. I have installed PostgreSQL-9.4 in master/slave setup(in pc1/pc2), and replication setup is successful. Setup of pooling is failing, I am following this ...
0
votes
1answer
40 views

PostgreSQL roles versus users, grant permissions

I'm currently learning the differences between PostgreSQL and MySQL as I've got a new project and I also intend to migrate my existing software from MySQL to PostgreSQL. I've actually started creating ...
0
votes
0answers
23 views

Does Postgres shutdown gracefully when I restart my Mac?

When I restart my Mac OS X computer, I assume the daemon controlling Postgres (the "Postmaster" as I recall) is notified by the OS and performs a graceful shutdown of any running databases with ...
0
votes
1answer
99 views

Error: /var/lib/postgresql/9.3/main is not accessible or does not exist

I have a PostgreSQL db that was running on my server. I tried to do a db_dump and got a weird error message. I eventually thought I would just restart PostgreSQL. However its now stopped and won't ...
1
vote
1answer
77 views

How to migrate postgreSQL on EC2 instance to AWS RDS?

I have a large PostgreSQL DB (around 100GB) which is on EC2. I want to migrate it to RDS. How can I achieve this with minimal downtime?
0
votes
1answer
28 views

Postgresql cluster over nfs

Can I create a HA postreges cluster using two different nodes via NFS? According to the postgresql 9.4 official doc this isn't a good idea but I don't find any other info on the web.
1
vote
1answer
42 views

Are PostgreSQL dynamic arrays more wasteful than plain text?

Today, someone was helping me to make a function in PostgreSQL. I was wanting the function to return in arrays of 100+ geometries per two columns instead of delimited text to make it easier to ...
1
vote
1answer
49 views

encrypt data without performance issues?

I've googled around for the last 45 minutes or so and it seems like that I can't figure out what kind of options I got when it comes up to encryption in databases. Problem: I got a SaaS product and ...
1
vote
1answer
33 views

Matching a value with every element of a set of values - Postgresql

I'm new in Postgres and I can't achieve what I'm trying to do. I have a list of films in a Films table ID_FILM FILM NAME 1 Terminator 2 Forrest Gump 3 Nueve reinas A ...
0
votes
1answer
52 views

why pg_restore ignores --create ? Error: failed: FATAL: database “new_db” does not exist

I am trying to run following command: sshpass -p "pass" ssh x@1.2.3.4 "pg_dump -Fc -U foo some_db" | pg_restore --create --dbname=new_db I get: failed: FATAL: database "new_db" does not exist
1
vote
1answer
27 views

script for restore of postgres database localy from dump given on stdin - ambiguous redirect

Runing this command: sshpass -p "pass" ssh x@1.2.3.4 "pg_dump -U foo some_db" runs perfectly fine. It returns on stdandard output the dump of db. Then I would like to restore new_db based on dump ...