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)

1
vote
1answer
5k views

How do I restore a plain text postgres .backup file

I exported a postgres db from pgAdmin in a plain format because I wanted to search the whole db text. I then tried to restore the database back but it gives an error saying that the archive file is ...
18
votes
4answers
11k views

Most idiomatic way to implement UPSERT in Postgresql nowadays

I've read about different UPSERT implementations in PostgreSQL, but all of these solutions are relatively old or relatively exotic (using writeable CTE, for example). And I'm just not a psql expert ...
5
votes
2answers
810 views

Span single transaction over several sequential psql sessions

I am running PostgreSQL 8.4 database server and psql terminal front-end on Ubuntu 10.04 Lucid Lynx and would like to span a single transaction over several sequential psql sessions. When I connect to ...
1
vote
1answer
187 views

Slony not replicating changes from a dump file

I’m working on a project where we are using pg_dump to get the data out of one database and then using psql to load it into another. The second database is the master db in a Slony replication ...
0
votes
1answer
168 views

How to improve restoring the PostgreSQL database?

I'd like to restore the PostgreSQL database from a dump. The below command works fine for me: CALL "%PGBIN%\pg_restore" -c -d NetCatalog %DUMPFILE% How can I add a few lines to my .bat file to ...
0
votes
1answer
2k views

How to select bytea data as binary data, inserted as hex-encoded from binary

The post How to insert (file) data into a PostgreSQL bytea column was helpful. However, when I select these columns, I get the same hex encoded data. For instance: > select ...
2
votes
1answer
307 views

Postgres - how to randomize two columns in a table

I have a table of User data which I would like to disguise/anonymize. Two of the columns have location data (e.g. lat and long, both BigDecimals). I have anonymized all the names and other ...
2
votes
3answers
610 views

Is there a PostgreSQL proxy running queries on several servers at once?

Let's assume we had PostgreSQL version X.Y and decided to give a test-try to version Z.W. Probably, there are some changes between X.Y and Z.W that could cause a little bit of trouble :-) It would be ...
2
votes
1answer
606 views

Table structure/schema for spreadsheet-like web app (ex: Google Docs)?

I'm a front-end developer/designer who is trying to branch out and learn more about the back-end world. I've chosen Python, Django, and Postgres as my starting place. My goal is to build a personal ...
1
vote
1answer
631 views

pg_restore fails for a pg_dump from a db that worked just fine

Any idea how this can happen? I have a table which I created by loading data into it via the COPY command. A column in it was declared as VARCHAR(200). I dumped the table, moved it to another ...
2
votes
2answers
164 views

Validation of availability for a new order

I have a management application of sales, stock and payment on a warehouse whole saler from a web interface. In particular, when a order is effectuated it must create a line corresponding to each ...
0
votes
1answer
5k views

Postgres Administration roles

I need to work with a remote postgresql server. My application needs to perform the following actions: to be able to create/drop database (I only need to work with 1 specific database) to be able to ...
3
votes
1answer
478 views

Dropped database still shows but causes errors PostgreSQL

I had dropped a database a couple weeks ago but it still shows with \l. I've tried using DROP DATABASE mixmobi_master; and the first time it gave me a confirmation message but mixmobi_master was ...
1
vote
1answer
194 views

postgresql - transaction log - versioning?

Is it possible to "version" your database so that any change can be reviewed or roll backed? Obviously backups are important but being able to go back to any previous state of the database (at least ...
3
votes
1answer
604 views

Ways to speed up IN queries under PostgreSQL

I have a query of the following form: SELECT * FROM twitter_personas WHERE twitter_user_id IN ($1, $2, $3, ..., $25000) The IN query has anywhere from 10 to 25000 values. The query runs for minutes ...
2
votes
2answers
737 views

How to specify trigger execution order under PostgreSQL?

I'm using "classic" time-based partitioning using triggers. I have found a need for a separate trigger, which runs on the original table. CREATE TABLE twitter_interactions(...); CREATE OR REPLACE ...
7
votes
2answers
429 views

Pros and Cons of Checking if value exist for unique column or let db raise unique error on inserting

While writing a query other day a thought came to me and have stuck in my mind. What is preferable, first checking if a value for a unique column exists and then inserting or insert and let db raise ...
8
votes
2answers
2k views

View Postgresql Memory Usage

I'm running Postgresql on a Ubuntu server and need to be able to monitor its memory usage. Currently I have scripts running in a one-minute cron job that monitor/log various statistics and need to ...
2
votes
2answers
770 views

Postgres Index scan forward vs backward = speed difference of 357X slower

PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-51), 64-bit Dedicated DB server 4GB ram Shared_Buffers = 1 GB Effective_cache_size = 3GB Work_mem = ...
4
votes
1answer
57 views

Want to add a “variable” to a table

I'm using PostgreSQL 8.4 and I have a table that is cleared out and refilled with new data every so often. I want to be able to store the date that the table was last filled, but I'm not sure where to ...
1
vote
2answers
379 views

Tables accessed during last periods in postgresql

I want to check that which tables are updated during the last period or say in descending order of accesstime of tables, then how can I view that for postgresql ?
2
votes
1answer
393 views

Postgresql revert back database value

I have postgresql database. My table name is tblvoippolicy. Now, problem is by mistake i have deleted some records from table. When I get the data file using following commands. select * from ...
0
votes
1answer
272 views

postgresql recovery after reboot

I installed Postegresql and added a database which I was using as part of a local server. However, I restarted the computer and my Postgresql database seems to be gone. The Postegresql documentation ...
3
votes
2answers
718 views

How does pgAdmin III build CREATE statements?

In PostgreSQL, there is no SHOW CREATE statement, known for example from MySQL. But when you use the pgAdmin III client program and go to object details, you see the corresponding CREATE statement. ...
4
votes
2answers
752 views

PostgreSQL unexplained table bloat

I have a table in a Postgres 8.2.15 database. The table bloated to almost 25GB but after running vacuum full and cluster the table size was dramatically smaller, well under 1GB. A few weeks later ...
0
votes
1answer
233 views

Slony - Wait for Event

What is the behavior of Slony waiting for an event after lock set command? Basically I am trying to do a switchover and it takes a lot of time. We are following these steps: Lock Set Wait for Event ...
0
votes
1answer
2k views

restoring a table from sql file in postgresql through Windows Command prompt

Im trying to restore an sql file which contains the dumpf a table (with COPY commands) into a table into the database, it would be great if someone could share an example, as the pg_restore doesn't ...
10
votes
2answers
9k views

Force drop db while others may be connected in postgresql

I need to remove a database in postgresql DB. How can I do it even if there are active connections? I need a sort of -force flag, that will drop all connections and then the DB. How can I implement ...
0
votes
1answer
253 views

Which file is PostgreSQL log file 0?

In my PostgreSQL logs, I find this message: LOG: invalid magic number 0000 in log file 0, segment 1, offset 16121856 Which file is log file 0? Here is my pg_xlog directory: -bash-4.1$ ll ...
3
votes
2answers
990 views

Should arrays of IDs stored in Postgres use varchar, int, or another type?

What is proper convention for creating a field that will hold an array of foreign keys? In this particular case, I'm creating a field called "owners" that will hold an array of user IDs. I want to ...
2
votes
1answer
2k views

Postgresql: Move database to new tablespace

I'd like to completely move a database (tables, indexes, etc.) to a new tablespace. I know how to move each item individually but is there a way to move it all in one go? I'm using postgresql 9.1.
1
vote
2answers
3k views

Running postgresql in Mac Terminal

I have a production SQL dump file that I want to restore to my local psql db for debugging reasons. I can't access psql via the terminal though pgAdmin3 works just fine. Here is the error I'm ...
2
votes
1answer
255 views

Streaming Replication on Two Postgres Systems?

There's two servers, a Primary Server and a Backup Server. Both have Postgres installed. The Primary Server has one database, and the Backup Server has two - one for a warm backup/standby of the ...
3
votes
2answers
500 views

How do I replicate SQL Server index INCLUDE and STATISTICS functionality on PostgreSQL?

I'm working on a project that must support two database engines; SQL Server and PostgreSQL. We are using NHibernate as the ORM. We are running into performance issues with certain queries. Using ...
2
votes
1answer
112 views

How to recover the database with old data?

I am using PostgreSQL. I have 4 database servers. Each server has same database and same schema. Mistakenly I restored the database util05db backup onto the util01db database. How can I get the ...
8
votes
2answers
4k views

PostgreSQL for high volume transactions and for Data warehousing

Am quite new to PostgreSQL, I have never done a large deployment using it before. But, I have good experience in enterprise solutions and I want to try and apply some of what I learned using ...
1
vote
1answer
915 views

Recover PostgreSQL database data

I am using PostgreSQL in my project, Now by mistake I have deleted some records of one of my tables. Now is there any way to recover deleted Records in PostgreSQL?
2
votes
2answers
1k views

Update column by comparing date and string values together

I have problem in updating table. I need to update table by comparing date and name with another table. I have two tables: table 1 : dim_sesid name(varchar) role ...
0
votes
1answer
1k views

PostgreSQL Query and/or Session CPU Usage

Is there any way in PostgreSQL to know how much the cpu cost was per a query and/or by session. It doesn't have to be the exact system cpu usage more of a relative cost like we get with disk I/O. For ...
2
votes
1answer
68 views

PostgreSQL + query planner + amount of entities

I'm working on college work about how query planner uses statistics and makes the most optimal query. I have read the 57.1. Row Estimation Examples article and I know how's the PostgreSQL calculates ...
3
votes
1answer
669 views

create index - invalid memory alloc request size

i get this error "invalid memory alloc request size 18446744072635840654. SQL Status:XX000" when i want to create an index. The Table looks like this: CREATE TABLE schema.tblname ( id bigint NOT ...
2
votes
2answers
768 views

Monitoring Progress of Index Construction in PostgreSQL

Is there a way to monitor the progress of the creation of an index in PostgreSQL. I am creating an index on a large table and I would like to see how fast this is occurring. Is there a way to monitor ...
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
4answers
4k 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
2k 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
189 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
791 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
3k 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 ...