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)

0
votes
1answer
184 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
3k 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
491 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
948 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
803 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
884 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
176 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
6k 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
556 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
212 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
998 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
656 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
3k 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
1k 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
58 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
590 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
653 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
517 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
991 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
1k 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
2answers
336 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
3k 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 ...
21
votes
2answers
18k views

Force drop db while others may be connected

I need to remove a database from a PostgreSQL DB cluster. How can I do it even if there are active connections? I need sort of a -force flag, that will drop all connections and then the DB. How can I ...
0
votes
1answer
361 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
2k 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
3k 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
271 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
930 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
113 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
7k 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
2k 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
2k 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
89 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
1k 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 ...
4
votes
2answers
1k 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 ...
8
votes
6answers
7k views

SQL Query Formatter [closed]

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 ...
4
votes
2answers
5k 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
198 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
190 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
1k 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 ...
20
votes
1answer
6k 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 ...
11
votes
1answer
4k 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
1k 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 ...
10
votes
2answers
5k 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 ...
23
votes
4answers
27k 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 ...