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
1answer
426 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
544 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
235 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
264 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
984 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: +----+--------+-----------+-----------+-----------+ | ...
3
votes
1answer
3k views

PostgresSQL dynamic execute with argument values in array

I'm wondering if this is possible in Postgres: Best explained using a contrived example: create or replace function test_function(filter_param1 varchar default null ...
4
votes
0answers
317 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 ...
6
votes
3answers
140 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
102 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
591 views

How to design tables for airbnb style booking system [closed]

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
77 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) ); I'...
1
vote
1answer
375 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
74 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 ...
2
votes
1answer
627 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
92 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 '<&...
6
votes
2answers
6k 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? ----------+---------- ...
2
votes
1answer
134 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 up....
1
vote
1answer
1k 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
144 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: ...
2
votes
1answer
2k 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
782 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
108 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
301 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 ...
2
votes
1answer
2k 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 ...
2
votes
1answer
602 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 NULL,...
0
votes
0answers
220 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
3answers
851 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
184 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
297 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
1answer
526 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
137 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
119 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 ...
5
votes
3answers
818 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
128 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
335 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 ...
1
vote
0answers
771 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
3answers
321 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. ...
2
votes
1answer
180 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
67 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 ...
4
votes
1answer
4k 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
86 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
195 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 ...
1
vote
1answer
372 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
1answer
3k 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
1k 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
402 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
72 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
204 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
78 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 ...