PostgreSQL is an enterprise class, open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.

learn more… | top users | synonyms (1)

1
vote
1answer
642 views

How to know how many lines are copied from table - psql

I want to write a script to backup a table using COPY. psql "connection parameters" -c "COPY (SELECT * FROM tbl WHERE insertion_date > 'date') TO STDOUT WITH CSV HEADER;" | bzip2 -c > ...
12
votes
1answer
20k views

How to use psql with no password prompt?

I wrote a script to REINDEX indexes in a database. Here is one of them: echo -e "\nreindex for unq_vbvdata_vehicle started at: `date "+%F %T"`" >> ${LOG_FILE} psql -U ${USERNAME} -h ${HOSTNAME} ...
3
votes
1answer
2k views

PostgreSQL Scheduled Database Backup

For scheduling a daily back up on a postgres database we execute the following command: 0 2 * * * /usr/local/pgsql/bin/pg_dump -Fp -b -U database_name \ > ...
4
votes
1answer
2k views

Dropping unique constraint on PostgreSQL table taking 10+ hours

We have a PostgreSQL database with a fairly large table (~50 million rows). The table has a unique constraint across several columns, each of which is indexed. For context, I recently deleted about ...
2
votes
1answer
2k views

How to import the data of postgres 8.x to postgres 9.x

My OS is windows 7 x64. I was use postgres 8.4, and all databases are in postgres/8.4/data. Yesterday, I reinstalled my os system, but I found I can't install postgres 8.4 anymore (there is always an ...
5
votes
1answer
4k views

How to get actually allocated tablespace size by each postgres database?

Postgres tablespace is cluster-wide. Therefore, how do I caculated how much space allocated by each database in postgres tablespace ?
4
votes
2answers
1k views

Is Tablespace something like Logical Volume?

I'm confused about tablespaces in PostgreSQL. Is it something like LVM? I mean when disk is getting full, can we add another disk, format and then CREATE TABLESPACE tblspace LOCATION ...
1
vote
1answer
625 views

Triggers not being run on a partitioned table

I'm using PostgreSQL. I have a table that I have partitioned into three sub-tables - and as far as I can tell, this all works correctly. Rows are correctly inserted into the appropriate partition and ...
1
vote
1answer
390 views

PostgreSQL: Trouble accessing another server's Data via NFS

Background: I'm working on a 2-node High Availability PostgreSQL database cluster for my company. The system has a master/standby setup where the standby takes over the duties of the master when the ...
4
votes
2answers
466 views

Huge database logging of event type rows and ways to optimize it

We have a database that stores events. Events are generated at a 1000 per sec rate. We need to keep these events accessible for some years. Usual use of these events is selecting some of them from the ...
4
votes
3answers
2k views

OCIEnvCreate failed to create environment handle after installing oracle_fdw

I have installed oracle_fdw successfully. After I create extension oracle_fdw and foreign table it shows the following error. Is this a server configuration problem? --env Oracle : 10.2.0.1 ...
0
votes
1answer
312 views

Update Postgres on server that already has version included with system

Mac OS X (Lion) Server includes Postgres (9.0.5) that it uses for some system utilities (Wiki server, caldav, etc.). I'm about to start using this server for some Rails applications and am wondering ...
7
votes
1answer
3k views

PostgreSQL Failover - What tools should I use?

Here is the scenario: There are two machines running CentOS 6.2 - machine0 and machine1 Both have PostgreSQL 9.1 installed. One of them should be active, as a master system and through asynchronous ...
1
vote
1answer
100 views

Impact of changing schema name

I have a server with two databases. Each database has a different schema name despite the fact that DB2 is a subset of DB1 (DB2 has a subset of the tables in DB1). Not withstanding any code changes ...
5
votes
4answers
2k views

Trouble setting up Postgres for the first time

I have some, albeit limited, experience with MySQL and MongoDB (both running fine on server) but having trouble with Postgres and find the documentation lacking for newbies, like myself. Postgres is ...
6
votes
1answer
2k views

Postgresql performed a “fast shutdown” by itself. Why? How was it able to do that?

I've already googled and manual read as much as I could before posting this here. Version info: Postgres8.4 on Ubuntu10.x I have a personal laptop that I run a postgres server on, and a Java script ...
2
votes
2answers
544 views

Joining based on date (or date range)

I'm not sure if I've completely fudged the design of my small hobby database (I'm not a DBA by any means), but I have a table like this (primary key is (staffid, effectivefrom)): staffid | target | ...
1
vote
1answer
256 views

Postgres shared_buffers and pooled processes

I have read several articles (including Postgres docs) that suggest setting the shared_buffer's config value to 25% of your total physical memory. Using my 4 GB memory as an example... If I ...
0
votes
1answer
117 views

What DML commands can be allowed / limited on a reporting server?

I have a largish database (PostgreSQL 9.1) which I'm preparing to replicate using streaming replication. The 2nd server will be used for reporting and backup purposes. When building reports, it's ...
1
vote
1answer
1k views

Postgres: trying to fix some unicode encoding issues

I've got a postgres 8.4 database with a few mangled characters in one of the columns on a table. For example, the word "don't" gets displayed within psql as: donâ\u0080\u0099t I want to be able to ...
6
votes
2answers
531 views

PostgreSQL Complete Database Backup

I've been trying to figure out how I can completely backup my PostgreSQL database. I have not seen any resource that explains how to do a complete backup. I am aware of the pg_dump and pg_dumpall ...
0
votes
2answers
253 views

Attempting to avoid SORT in query planner

I have a query that is used VERY frequently in my application. Without modifying the query itself, I'm trying to optimize it by toying with different indexing schemes. Here's the query (basically): ...
2
votes
1answer
661 views

PostgreSQL partitioning query performance inexplicably bad

I find this a bit mysterious. The cost of getting the maximum value out of just the partition parent table (which is empty) is very cheap: evlampts=# explain select max(timestamp) from only mcdata; ...
1
vote
2answers
963 views

Postgresql 9 table lock resolution

I have an ALTER TABLE statement that according to pg_stat is "waiting", and I'm wondering if there is an easy fix to allow the statement to execute? From my limited understanding I think it's a ...
9
votes
1answer
376 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
218 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
2k 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 ...
2
votes
1answer
7k 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 ...
22
votes
4answers
17k 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
1k 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
206 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
181 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
392 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 ...
3
votes
3answers
764 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
708 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
794 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
169 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
523 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
205 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 ...
4
votes
1answer
827 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
1k 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
554 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 ...