Tagged Questions

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)

9
votes
1answer
360 views

Disk file effects of delete and vacuum

I have a very frequently updated table with 240 million rows (and growing). Every three hours 1.5 million rows are inserted and 1.5 million are deleted. When I moved the cluster to a SSD this bulk ...
1
vote
1answer
204 views

Slow enumeration of schemas and tables in a largish database

We have a Postgresql 8.4.8 database server on a Debian system with a database that has 500+ schemas with roughly 300+ tables each. The schemas and tables are created by scripts, we cannot influence ...
2
votes
1answer
1k views

WAL backups with pg_dump not possible?

I am hoping to move to WAL backup from pg_dump. But I am confused by this note in 4.3 documentation: Perform the backup, using any convenient file-system-backup tool such as tar or cpio. And ...
6
votes
2answers
3k views

Does running pg_dump on live db produce consistent backups?

I have a 3GB database that is constantly modified and I need to make backups without stopping the server (Postgres 8.3). My pg_dump runs for 5 minutes. What if the data is modified during the ...
2
votes
1answer
2k views

PostgreSQL dump/restore from 8.4 to 8.1 in a shell script

I have to restore a DB table every day from a postgresql 8.4 to 8.1. For the backup, I use this code (windows server): D:\PostgreSQL\8.4\bin\pg_dump -U postgres -p 5433 -t my_table myDB > ...
6
votes
2answers
1k views

Database for building a realtime analytics system

I want to build a system similar to Google Analytics (only used for internal use, less traffic and less feature), and mainly focus on Real time counting of unique URI visit/PV by different ...
2
votes
1answer
2k views

Error importing back a postgres db after dumping

I dumped my postgres db using pgAdmin in plain format because I wanted to search for some text on it. After doing the search, I decided to import the db back from the sql query window but I get this ...
2
votes
2answers
1k views

Slony lib not found

Trying to run the example slony config script in the documentation but getting the following error: postgres$ /tmp/slonik_example.sh <stdin>:8: PGRES_FATAL_ERROR load '$libdir/slony1_funcs'; ...
3
votes
2answers
1k views

Fastest validation query in PostgreSQL

What is the fastest query in PostgreSQL, which I can use as a validationQuery binding a JNDI resource? I though that SELECT 1 is the simplest, but in this document it is said that in PostgreSQL we ...
1
vote
1answer
6k 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 ...
21
votes
4answers
15k 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
919 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
198 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
175 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
349 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
673 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
680 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
742 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
168 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
506 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
200 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
762 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
891 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 ...
8
votes
2answers
496 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
865 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
427 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
489 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
340 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
769 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
867 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
266 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 ...
13
votes
2answers
11k 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
286 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
1k 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 ...
3
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
4k 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
259 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
602 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
5k 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
1k 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 ...
3
votes
1answer
79 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
808 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 ...