PostgreSQL is a powerful, enterprise class, open source RDBMS. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability and data integrity. It runs on all major operating systems, including Linux, UNIX and Windows. It prides ...

learn more… | top users | synonyms (1)

0
votes
1answer
78 views

pgAdmin on a fresh OS X Server installation

I know for sure postgresql is running (checked with ps ax), but when I install pgAdmin and try to make a server connection to localhost I get this error: Server doesn't listen The server doesn't ...
3
votes
1answer
123 views

How do I decompose ctid into page and row numbers?

Each row in a table has a system column ctid of type tid that represents the physical location of the row: begin; create table t(id serial); insert into t default values; insert into t default ...
3
votes
1answer
58 views

Can Postgres synchronous replication stall a cluster if secondary/standby crashes?

In a setup with Postgres synchronous replication, let's say one of the secondary/standby machines crashes, will that lock/halt the master? That is, since the Postgres master always waits for ...
1
vote
1answer
51 views

What is the meaning of WAL file name?

We know WAL file locate in the $PGDATA/pg_xlog directory, but I don't know the meaning of WAL file ,such as a WAL file which named "0000000A00005283000000E0", I see some's blog say WAL file name ...
1
vote
0answers
59 views

Does a serialized transaction affect rows written after begin?

I'm trying to determine exactly how a SERIALIZABLE transaction affects rows written after the transaction has begun. I'm using PostgreSQL 9.2. For example, in one of my serialized transactions, it ...
1
vote
0answers
46 views

appending 2 tables based on common columns in Postgresql 9.3

I have 2 large tables that I need to combine into a single table--for statistical analysis. The two tables have some variations in column named, otherwise I would just use an INSERT statement. So I ...
7
votes
1answer
83 views

How do data checksums interact with streaming replication?

Data checksums are a new feature introduced in 9.3, and: there is a new GUC parameter "ignore_checksum_failure" which will force PostgreSQL to continue processing a transaction even if corruption ...
0
votes
2answers
34 views

Get distinct results based on column and satisfy condition

I have the below select which is selecting from a table with the columns: id, project_id, keyword_id, position, Data example: i, p_id, k_id, p 1, 1, 1, 4 2, 1, 1, 5 3, 1, 1, 6 5, ...
0
votes
0answers
39 views

COPY TO .csv and add quotes to header in PostgreSQL

I use the following in the command line \copy (select id, pid from table1) to '/path/to/file.csv' with delimiter ';' CSV HEADER; Everything works fine. But I want the header's string to be quoted ...
0
votes
0answers
50 views

How to avoid locking problems while disabling/enabling triggers in a PostgreSQL function?

Whenever I try to disable a trigger from within a function, I get following error: ERROR: cannot ALTER TABLE "datace" because it is being used by active queries in this session It seems logical ...
1
vote
1answer
35 views

Is there a way to define macros/alias in psql?

It is somewhat annoying to type EXPLAIN (ANALYZE, BUFFERS, VERBOSE) in psql tool every time you tune queries. So, is there a way to define an aliases (autocompletable ones would be very nice) in psql ...
0
votes
1answer
158 views

Postgres: How to insert row with autoincrement id

There is a Table "context". There is an autoincrement id "context_id". I am using sequence to retrieve the next value. SELECT nextval('context_context_id_seq') The result is: 1, 2, 3,...20.... But ...
0
votes
1answer
79 views

UPDATE Query replacing string primary key taking days to resolve

I'm working with a DB (not designed by me) that had the primary key set to a 47 character length string, which is causing problems due to the fact that the table has over 20 million rows, and this ...
5
votes
1answer
131 views

HStore vs. multiple tables with inheritance - do I have a good use case for HStore?

My broader question here has to do with when it is appropriate to use HStore vs. multiple tables vs. one table for storing document-like objects. Now, for my localized example: I’m designing a ...
0
votes
1answer
34 views

Postgresql database backup (and restore) from two servers to third storage place

I have two server machines with PostgreSQL: (A) is version 9.0.15 and (B) version 9.2. I wish to make backups of the databases stored in (A) and (B) - and be able to do restore if needed too! - to a ...
1
vote
1answer
80 views

Conditonally create temporary table

Invoking the following SQL leads to a warning 'column "s" has type "unknown"' which I can understand. What I'm trying to accomplish is to conditionally create a temporary in preparation of running a ...
1
vote
1answer
66 views

INSERT from a SELECT combined with a single value

I have this query that returns several rows of item_id: select item_id from properties where name like 'body'; And I have the second one that returns 1 row of tag_id: select id from tags ...
2
votes
1answer
59 views

Why is vacuum at a critical level in PostgreSQL when I'm running autovacuum?

I just checked the state of vacuum in my PostgreSQL database and it's at a critical level: $ check_postgres --action=last_vacuum POSTGRES_LAST_VACUUM CRITICAL: DB "postgres" DB: postgres TABLE: ...
2
votes
1answer
52 views

Postgresql AUTOVACUUM ANALYSE parameters for a specified table

I would like to set autovacuum parameters for a specified table. My problem is that AUTOVACUUM ANALYSE on a small table takes way to long to complete. This table has only 2 columns (including the ...
0
votes
1answer
32 views

Restricting Insert and Update to functions

We have a function in postgresql that inserts some rows in a table and updates some other rows based on an input provided by the user. Is it possible to somehow execute the function without giving ...
0
votes
0answers
30 views

Incremental backup of select PostgreSQL databases

There is lots of information on how you setup archiving by backing up WAL segments (and in fact I'm currently doing this successfully). However we have a lot of databases in our cluster (one per ...
0
votes
1answer
49 views

PostgreSQL replication with fsync disabled?

I need to run PostgreSQL in-memory (for performance reasons), so I intend to disable fsync, which means that no writes will be sent to the WAL. However, as part of my scheme to meet another ...
0
votes
0answers
23 views

How can I figure out what service is trying to connect as root?

In my postgresql log I see this from time to time: 2014-05-20 12:34:03 CEST FATAL: role "root" does not exist I would not expect any of the services that I have configured to connect as root. How ...
0
votes
1answer
84 views

Postgres live backup

Goal: Maintaining a constant live backup of a postgres cluster. In case of cluster failure, backup/Secondary will contain most up to date view of Primary cluster. This is not a hot standby ...
0
votes
0answers
36 views

PostgreSQL manual way of sharding like behaviour

I've a main server with low space available and good internet connection that has a postgres database in production for a zabbix system. Also, I've another secundary server plenty of space but with ...
0
votes
0answers
92 views

Postgres 9.3 64 Bit Windows 7 and 64 bit Postgres install - cleared password no longer connects

Originally I allocated a password to the postgres database, but for some stupid reason the servers are usually setup with a blank password. I then assigned it a blank password in PGAdmin III, and it ...
2
votes
1answer
52 views

How to create a role that cannot create or alter a table?

I want to create a role named manager in postgresql . This role can update, select, delete and insert data in all tables but users that belong to this role cannot create a table nor modify the schema. ...
2
votes
1answer
62 views

Is this inconsistent treatment of `void` return from sql and plpgsql functions documented?

begin; create table foo(id integer); create function f1() returns void language sql as $$delete from foo;$$; create function f2() returns void language plpgsql as $$begin delete from foo; end;$$; ...
1
vote
1answer
146 views

Multiple primary keys in PostgreSQL

I have the following table: CREATE TABLE word( word CHARACTER VARYING NOT NULL, id BIGINT NOT NULL, repeat INTEGER NOT NULL ); ALTER TABLE public.word OWNER TO postgres; ALTER TABLE ONLY word ADD ...
1
vote
1answer
412 views

SQL query to find all parent records where all child records have a given value (not just some)

An Event has many Participants. A Participant has a field of "status". I need to find all Events except the following ones: Events where every one of its participants has a status of 'present'. I ...
0
votes
1answer
55 views

Automatically rollback two-phase transactions on session disconnect

I'm using two-phase commit in Postgres to reliably work with an external system (Amazon EC2). I.e. I first do a PREPARE call, then call the external system. If the external system call fails for some ...
1
vote
1answer
51 views

How can I load 'utf8' into plperl by default?

I am attempting to solve a problem explained in another question here. On Debian 7.4 with Postgres 9.1.12, I am able to use the Perl qr operator just fine. On Solaris 5.11 with Postgres 9.2.4, ...
1
vote
2answers
112 views

Dynamic UPDATE fails due to unwanted parenthesis around string in plpgsql

Using PostgreSQL 9.3.4 on Mac OSX 10.9.2 with Postgres.app. I want to apply a function to all columns in a table, specifically I want to trim white space. So far I've got this in a file test.sql: ...
1
vote
0answers
86 views

PostgreSQL refuses to listen on IP [closed]

This was a case of PEBKAC. By default, the listen_addresses line in postgres.conf is commented out. I failed to notice this and remove the comment. I'm trying to get a PostgreSQL 9.3 server up and ...
0
votes
0answers
109 views

how to append or update JSON datatype value in postgreSQL

hey guys i'm using PHP's codeigniter to create my application and postgreSQL as database and i have to update some value in database. So, let's say we have table structure like this:- CREATE TABLE ...
0
votes
0answers
41 views

Error while taking backup in Postgresql

While taking a backup from my PostgrSQL database, it is showing this: pg_dump: Dumping the contents of table "gtab17" failed: PQgetResult() failed. pg_dump: Error message from server: ERROR: invalid ...
2
votes
0answers
85 views

Converting a SQL_ASCII database to UTF-8

I have a postgresql (version 8.1) database that has the encoding set to SQL_ASCII, combined with several apps that some are running using UTF8 and some with LATIN1. With this scope I have a database ...
5
votes
2answers
126 views

Indexing a large static dataset

My current situation is this: I have 328 million lines of data in a flat csv file. Not optimal. I want to be able to query this data (I'll explain in more detail). Grep is getting tired. This data is ...
0
votes
0answers
42 views

Mirror database on same server instance for better read performance?

I am developing a somewhat big web application, which uses data which is aggregated using a crawler. The crawler is constantly writing to the database and updates many tables at the same time. For the ...
8
votes
1answer
256 views

How do I 'hint' the cardinality of a recursive CTE?

I'm using the following recursive CTE as a minimal example, but in general, the optimizer has to use default 'guessed' cardinalities for recursive CTEs: with recursive w(n) as ( select 1 union all ...
1
vote
1answer
38 views

Why is logging “slow”?

On an empty transient table, inserting a single row took 50ms before changing it to UNLOGGED, 1ms afterwards. Why is this exactly? If the table structure is necessary, I'll post it, but it's only ...
1
vote
1answer
29 views

How do I automatically set meta commands when starting psql?

When I start a psql session, I would like certain meta commands to already be set. For instance: \x auto \set ON_ERROR_STOP on I was hoping to find an rc or config file but couldn't. The command ...
2
votes
0answers
46 views

Postgres don't start after an unexpected shutdown

After an unexpected shutdown in postgres 8.4, I'm getting this error. couldn't open the file 'pg_xlog/....'. No such file or directory. invalid primary checkpoint record. Complete message in ...
0
votes
1answer
73 views

How can I pg_dump my database while excluding extensions?

I am trying to migrate my PostgreSQL 9.3 database to heroku. My database uses the UUID-ossp contrib extension I am not able to load my db onto Heroku because ERROR: permission denied for language ...
1
vote
0answers
85 views

Using prepared statements in libpqxx with variable numbers arguments

I am trying to do some streamlining of a PostgreSQL database application. I am collecting data in a real-time setting using a Windows application and libpqxx. I would like to look into using ...
0
votes
1answer
82 views

Query improvements without UNION

I have the following scenario. A users table, a records table and a memberships table. A user can be associated to zero or more records either directly with the records table records.user_id = ...
1
vote
0answers
32 views

pgpool stability issues

I am using pgpool-II 3.3.1 to load balance connections to my postgres 9.3. I am experiencing an ocassional stability issue where clients are not able to connect to pgpool (but they are able to connect ...
1
vote
1answer
60 views

Easiest way of shutting down a PostgreSQL server on Ubuntu

According to pg_lsclusters I have 2 servers running on Ubuntu 14.04: Ver Cluster Port Status Owner Data directory Log file 9.1 main 5432 online postgres ...
1
vote
0answers
180 views

Setting password for 9.3 PostgreSQL server

I currently have a 9.3 server running (on Ubuntu 14.04) with "trust" set as the authentification method in pg_hba.conf. How can I set the password again. In pgadmin the server currently doesn't allow ...
2
votes
1answer
38 views

How can I enable logging for a single function in PostgreSQL?

I want to log the whole function runtime for a single particular function in PostgreSQL? Is this possible? The value of log_min_duration_statement = 3000 in postgresql.conf and I've tried to do: ...