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)

4
votes
1answer
2k views

PostgreSQL streaming replication broken: invalid magic number, out-of-sequence timeline ID

For some reason, my PostgreSQL slave no longer streaming-replicates changes on the master. It was working before, for a while, but I recently noticed that the slave's database contents is old, and ...
5
votes
5answers
5k views

SQL Query Formatter

Are there any (Linux based) SQL Query Formatting programs/plugins/extensions? I use PostgreSQL and MySQL but other DB's are welcome as well. I can use a VM to test with but would prefer a Linux ...
3
votes
2answers
3k views

List primary keys for all tables - Postgresql

Is there a query that will do that? I found some queries that can do this for one table, but I wasn't able to modify it so I can see: tablename | column | type
0
votes
2answers
191 views

New PostgreSQL 9.1 configuration doesn’t work

I changed the length of table names in PostgreSQL in the file pg_config_manual.h (in C:\Program Files\PostgreSQL\9.1\include) to 128. Then I reloaded the new configuration by selecting menu "Reload ...
4
votes
2answers
180 views

Deadlock during Slony uninstall and concurrent access of DB

I am using slony1-2.0.4.rc2 and postgresql-8.4.0-0. I faced a deadlock in the slony uninstall process. I was trying to uninstall node from a cluster, as per slony's documents this requires access ...
5
votes
1answer
818 views

How to calculate cache misses for PostgreSQL

I'm administering a server where a tool using a PostgreSQL runs. The tool takes care of most PostgreSQL configurations by itself, but I'm observing some performance problems. I could confirm at OS ...
1
vote
2answers
1k views

Backup of Postgres database

I want to create a backup of my database in Postgres. I'm using pgAdmin. on my database I have clicked right button of mouse after that I've selected the "Create a backup of database" After that ...
17
votes
1answer
4k views

Best practices for schema changes and data migrations to a live database without downtime?

How do you make schema changes to a live database without downtime? For instance, lets say I have a PostgreSQL database with a table including various user data like email addresses etc, all ...
8
votes
1answer
3k views

PostgreSQL failover and replication

I'm evaluating PostgreSQL 9.1 and have few questions related to failover and replication details. I have few test scenarios. First one with a Master server and few Slaves. In case Master crashes I ...
3
votes
1answer
861 views

Working across multiple PostgreSQL database servers

So we are writing an app whose schema should reference data which lies currently in an external PostgreSQL instance. We are negotiating being able to put our schema within the external database, but ...
8
votes
2answers
4k views

PostgreSQL vs. MySQL - Advantages / Disadvantages with a spatial component

We are the in the process of building out a web application that has a spatial data component. In the beginning our spatial data comparisons will take a given point and return matched overlapping ...
17
votes
4answers
13k views

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

I had to write a simple query where I go looking for people's name that start with a B or a D : SELECT s.name FROM spelers s WHERE s.name LIKE 'B%' OR s.name LIKE 'D%' ORDER BY 1 I was wondering ...
2
votes
1answer
436 views

Restoring a postgres8.3 backup with postgres8.4

I'm trying to restore a backup dumped from a postgres 8.3 server in a server with postgres 8.4 with this command: pg_restore --host host --port 5432 --username username --dbname my_db ...
3
votes
1answer
478 views

How can I escape XML field in XML-output query?

I have following request: SELECT XMLAGG(data) FROM (SELECT XMLELEMENT(name item, XMLFOREST( title, body as description, date_created as date, 'http://example.com/news'||id as link )) AS ...
3
votes
1answer
2k views

Copying the database from SQL Server to PostgreSQL

I currently have SQL Server 2005 and I want to make a copy of the entire database into PostgreSQL. I don't want to migrate to new PostgreSQL but just a entire copy. I almost have more than 1 TB of ...
2
votes
1answer
271 views

Rewriting a query

I have a database with the following structure (star schema): facttable: pros integer mena integer me integer vahet double precision slutttp timestamp without time zone Structure of each ...
3
votes
1answer
2k views

Postgres PgAdmin III Query Builder crash

I've been using Postgres and pgAdmin III for some years, but this morning I had a very BIG BIG problem. I'm using pgAdmin III 9.0 on Windows Xp operating system, on my laptop Dell Latitude E4300. ...
8
votes
2answers
2k views

Can PostgreSQL create a case sensitive database?

I'm porting some code from some version of Sybase to PostgreSQL. This is a C application that uses the Sybase client library. My approach is to write a translation layer that translates calls to ...
9
votes
1answer
5k views

PostgreSQL Database Data File Integrity Check

When running a PostgreSQL database system how do I know my database as a whole has 100% integrity? Basically how do I know if my data files and pages are all 100% good with no corruption? In the ...
11
votes
3answers
2k views

Does PostgreSQL 9.1 Streaming Replication catch up after a lag without WAL archiving?

Context: Let's say that, while using Streaming Replication/Hot Standby on a Postgres 9.1 cluster, a standby node goes down. It stays down for an day, during which time a lot of DML occurs on the ...
2
votes
1answer
340 views

Why is a duplicate record inserted when it should not be?

Why does this command insert into "deliveryLog" select * from temp_dellog where "deliveryID" < 15620652 give this output? ********** Error ********** ERROR: duplicate key value violates unique ...
5
votes
2answers
4k views

Is there a simple way in PL/pgSQL to check if a query returned no result?

I'm currently experimenting a bit with PL/pgSQL and want to know if there is a more elegant way to do something like this: select c.data into data from doc c where c.doc_id = id and c.group_cur > ...
11
votes
4answers
16k views

PostgreSQL: How to create full copy of database schema in same database?

How I can copy my public schema into the same database with full table structure, data, functions, fk, pk and etc. My version of Postgres is 8.4 P.S. I need to copy schema NOT database
2
votes
1answer
400 views

Query takes hours

Up until now all queries in my PostgreSQL application were fast. During the last days, sometimes a query takes several hours. Index are healthy, because after dump, restore, vacuum -z, it is still the ...
10
votes
2answers
2k views

Streaming replication and failover on PostgreSQL

I am doing a proof of concept on PostgreSQL replication. After the discussion on forum we decided to go with streaming replication as the performance is good compared to other solutions. PostgreSQL is ...
6
votes
1answer
266 views

in postgres, can one optimize a table across partitions?

in a database with many companies, segregating them using schema and search_path almost all access comes at the company level, that is, limited to a single schema. some data-warehouse and accounting ...
2
votes
1answer
925 views

hstore for versioning fields in postgresql

I have a postgresql database that will hold about 50 tables, each of them having about 15 fields, it would have at least 300.000 rows on each table. In order to track the changes done on each field I ...
6
votes
5answers
10k views

PostgreSQL: The database cluster initialization failed

Both C:\PostgreSQL and C:\PostgreSQL\data have postgres user with full access and admin rights. I ran postgresql-9.1.2-1-windows.exe from postgres user as admin. Target C:\PostgreSQL Every way I ...
13
votes
2answers
7k views

Configuring PostgreSQL for write performance

One of my PostgreSQL servers hosts several (1-3) databases which receive a constant stream of data. The data is not particularly structured, it amounts to the current time and a variety of observed ...
2
votes
1answer
926 views

How to make it impossible for a postgres user to delete databases?

What yould be the sql statement (that the user postgres will execute) so it will be impossible for the postgres user user1 to delete (drop) databases? Or can I add a rule into some config file?
9
votes
3answers
4k views

How to convert mysql to postgresql?

I am desperately looking for a conversion tool to convert a big mysql database to postgresql. I need the tool/script to be: Free Works under Linux Simple to use and not buggy You actually tried and ...
4
votes
1answer
745 views

Database as a service (DbaaS) over dimensions--performance, scalability, & reliability?

MySql Database as a service: Amazon Xeround Another eg. PostgreSQL Database-as-a-Service. You can get the list of similar products here. Does anyone have details on their performance, ...
2
votes
1answer
2k views

Postgresql 9.1 replication delay

I just set up a PostgreSQL 9.1.2 database server on debian squeeze. I configured a standby server (hot standby) on a second identical server (squeeze, PG 9.1.2). On the master I set these parameters ...
1
vote
1answer
262 views

Using PL/PerlU with a non system perl lib

psql -1f check_email.pl.sql ccushing postgres SET psql:check_email.pl.sql:7: ERROR: Can't locate Email/Valid.pm in @INC (@INC contains: /usr/lib/perl5/site_perl /usr/share/perl5/site_perl ...
6
votes
4answers
5k views

No NULLs, yet invalid byte sequence for encoding “UTF8”: 0x00

I've spent the last 8 hours trying to import the output of 'mysqldump --compatible=postgresql' into PostgreSQL 8.4.9, and I've read at least 20 different threads here and elesewhere already about this ...
4
votes
2answers
1k views

PostgreSQL query with max and min date plus associated id per row

I have the following table: CREATE TABLE trans ( id SERIAL PRIMARY KEY, trans_date date, trans_time time ); I want to have the following view CREATE OR REPLACE VIEW daily_trans ...
0
votes
0answers
46 views

postgresql view [duplicate]

Possible Duplicate: postgresql view with max min with id I have the following table: CREATE TABLE trans ( id SERIAL PRIMARY KEY, trans_date date, trans_time time ); I ...
3
votes
1answer
2k views

Any Idea how to migrate from PostgresSQL 8.4 to PostgresSQL 9.1.1 in opensuse12.1

I had a crashed opensuse 11.3 that's why I installed opensuse 12.1.. after I installed (via yast) and edited /etc/passwd I do this: rcpostgresql start and this shows on the /var/log/messeges ~ ...
19
votes
3answers
11k views

PostgreSQL multi-column unique constraint and NULL values

I have a table like the following: create table my_table ( id int8 not null, id_A int8 not null, id_B int8 not null, id_C int8 null, constraint pk_my_table primary key (id), ...
4
votes
2answers
3k views

Is there a means to set the owner of all objects in a PostgreSQL database at the same time?

Modify OWNER on all tables simultaneously in Postgresql describes some nifty ways to change table and other objects to a specific user, and it works swimmingly, however all the suggestions seem to ...
3
votes
2answers
4k views

PostgreSQL: Query for location of global tablespace?

I have been trying to make our database clients pro-active about not filling up the partition on which the database they are using resides. As all our clients are on the same host as the database ...
9
votes
3answers
10k views

Automating failover in PostgreSQL 9.1

How does one setup two identical servers for automatic failover in PostgreSQL 9.1. OS Centos 5 PostgreSQL 9.1 compiled from source The postgres user account exists on both machines and has a ...
1
vote
1answer
1k views

64 bit Postgres 9.1 Limitions on Windows 2008 R2?

When Deploying Postgres on Windows Server 2008 R2: Are there any limitations on windows that don't exist on Linux? Does the postgres scale in the same way on windows as it does on Linux? Is ...
1
vote
3answers
230 views

PostgreSQL and query planner

I have question about query planner in PostgreSQL. I know, that this planner is taking data from pg_statistics, but... Can anyone tell me on what basis this planner is taking those informations to ...
4
votes
3answers
5k views

PostgreSQL vs. Oracle High Availability solutions?

PostgreSQL has a matrix of different high availability options which represent many different ways of building replication into an RDBMS. Here is the PostgreSQL High Availability, Load Balancing, ...
2
votes
3answers
372 views

Replication chaining with PostgreSQL

We have an unusual replication scenario and I am not sure which tool would be appropriate for that. Basically, we have a legacy non-normalized database that we want to refactor into a normalized ...
3
votes
1answer
1k views

Postgres uuid: Use as primary key, or in addition to SERIAL -for disconnected app-

For a new SASS app with postgres 9, that will support disconnected clients. The client not will use the regular IDs, only the UUID. The clients will be on iOS, html5/python and perhaps later on other ...
3
votes
2answers
500 views

How do I configure PostgreSQL 8.3 to log bind parameters?

I'm running into some unique constraint problems with my database. I can't reproduce them, but see error messages from our production system: exception 'UniqueViolation' with message ...
6
votes
1answer
511 views

PostgreSQL partition hierarchy design for centralized logging

I am looking into setting up logging from all of my servers into a centralized postgresql database. It makes sense to be able to drop logs by date or by host, so I'd like to set it up with ...
3
votes
2answers
967 views

VACUUM and VACUUM FULL do not return space to OS

I'm running Postgres 8.3.3. I have a postgres database on a Linux filesystem that is quite full. I've run VACUUM and VACUUM FULL, yet none of the space has been returned to the OS. When I observe ...