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)

2
votes
1answer
178 views

Which database to use? [closed]

Hey there. I am looking for a free to use for commercial website database that should have these design features, in the given order: Auto-Sharding (getting more free space by adding more server ...
3
votes
1answer
1k views

SET extra_float_digits = 3

I'm using postgresql 9.2 with my web application that has three servlet with three select. I turn on the log. When i send my first query: 'select * from "Nomi" limit 4' before this the log show me ...
4
votes
1answer
3k views

ERROR: database “dbname” does not exist

According to the documentation, so long as I'm not connected to a database, I can either delete a database in the console using: DROP DATABASE dbname; Or I can drop it using the wrapper tool ...
0
votes
1answer
66 views

PostgresSQL: Cannot get to console under different user name

I normally get to the console in UBUNTU by typing: sudo -u postgres psql But I need to delete a database owned by a different user. When I type: sudo -u username psql It tells me "database ...
3
votes
2answers
390 views

How do I solve an error code 127 when doing a pg_dumpall through a cronjob?

I have this simple script to backup all my databases in postgres pg_dumpall -U pg_user > /tmp/tmp.txt DUMP_STATUS=$? echo "Dump status: $DUMP_STATUS" > /tmp/status.txt The dump ...
5
votes
1answer
3k views

pg_dump and ERROR: missing chunk number 0 for toast value

I'm using PostgreSQL 8.4.15. While running pg_dump to backup a database, I got the following error: pg_dump: SQL command failed pg_dump: Error message from server: ERROR: missing chunk number 0 for ...
0
votes
3answers
845 views

Postgresql 9.2 won't start after rerunning installer

All, I made a fatal mistake and accidentally ran the core installer for postgresql rather than the stackinstaller. I didn't go all the way through the install process but it still set something that ...
0
votes
1answer
281 views

Why doesn't more memory necessarily make Postgres more performant?

The Postgres resource consumption docs state, under the shared_buffers: ...it is unlikely that an allocation of more than 40% of RAM to shared_buffers will work better than a smaller amount. Why ...
1
vote
3answers
3k views

Computer restarted and Postgres 9.2 service cannot be started?

My firewall is down and when I tried to start the Postgres service it gave me an error that said: the service started and then stopped. Some services stop automatically if they are not in use by other ...
16
votes
5answers
3k views

Best cloud platform for PostgreSQL [closed]

Currently, in our project, we use dedicated servers for our PostgreSQL database. In theory, we can run anything on some cloud platforms. However, PostgreSQL configuration is strictly related to ...
1
vote
2answers
7k views

Connect to Postgresql 9.2 from Eclipse Data Tools Platform

GOAL: My goal is to write sql code within Eclipse to query a Postgresql database. The reason I want to write my queries within Eclipse is to then write R or Python code to perform statistical analysis ...
3
votes
3answers
802 views

Backup a sample of a PostgreSQL database

Is it possible to back up a PostgreSQL database with only a fraction of the data (1000 Rows from each table)? I am able to back up that database using pg_dump. In some case I need db with minimum ...
3
votes
1answer
190 views

Bayesian ranking

I have the following table in my database: ╔════════╦═════════════╦═══════════════╗ ║ Name ║ total_stars ║ total_reviews ║ ╠════════╬═════════════╬═══════════════╣ ║ Item A ║ 27 ║ ...
5
votes
3answers
832 views

“AT TIME ZONE” with zone name PostgreSQL bug?

I was answering this stackoverflow question and found strange result: select * from pg_timezone_names where name = 'Europe/Berlin' ; name | abbrev | utc_offset | is_dst ...
4
votes
1answer
113 views

Postgresql, write archive file over mount, secure against simultaneous access?

I am using Postgres 9.2.1 and am saving my archivable WAL over a NFS share. I just use the basic command, given as an example in the postgresql.conf test ! -f /mnt/server/archivedir/%f && cp ...
6
votes
1answer
231 views

Privileges required to install PostgreSQL

I am new to PostgreSQL Database. I need to install it in my machine. I am not administrator in my system. Can some one suggest, if Non-Administrator can install it. If there is any other way of ...
4
votes
1answer
832 views

Postgres scalability - What is the impact of connection pooling?

We had a question over on Server Fault that raised an interesting question: In light of the scalability improvements in Postgres 9.2 scalability improvements, is it better to use connection pooling ...
1
vote
1answer
154 views

Parse hyphenated word to unsigned integer

When tested to_tsvector on 'someword-123' with english full text config, I got the following result: SELECT to_tsvector('someword-123'); to_tsvector tsvector -------------------- "'-123':2 ...
2
votes
4answers
2k views

Cannot shutdown old postmaster when upgrading to Postgres 9.2

I'm upgrading to Postgres 9.2.2 (from 9.1.4). When I try to upgrade the DBs using: pg_upgrade -b /usr/local/Cellar/postgresql/9.1.4/bin -B /usr/local/Cellar/postgresql/9.2.2/bin -d ...
3
votes
2answers
2k views

Running pg_dump on a hot standby server?

Disclaimer: I admittedly haven't tried this yet, but I'm not sure I would know if it wasn't working correctly, so I wanted to ask. I would like to run a nightly backup job (via pg_dumpall) from a hot ...
6
votes
2answers
2k views

PostgreSQL: Can I do pg_start_backup() on a live, running db under load?

Our established replication has broken ("requested WAL segment has already been removed" during downtime) We cannot easily stop the master again. Can we do pg_start_backup(), rsync ${PGDATA}/ ...
4
votes
1answer
992 views

What is actually happening when postgresql discovers the trigger file and promotes it self from slave to master?

I have a streaming replication setup between two postgres servers (master: server A, slave: server B). I'm wondering what's actually happening under the hood when I touch the trigger file and the ...
1
vote
1answer
655 views

How can I ensure that in a insert trigger a column gets filled before other column?

The bigger picture: tableX represents pipelines and tableY streets. A pipeline crosses/goes along many streets, and I want to know which street is crossed the most. First, I am segmentizing each ...
1
vote
1answer
225 views

How to setup a PostgreSQL cluster

Could you point me to a good book about setting up a PostgreSQL cluster? The goal is to set up an active-passive cluster spread across two different data centers to achieve high availability.
2
votes
2answers
385 views

Combining two similar SQL queries

I'm using the following SQL query twice with different values of limit. In my case, 40 and 100, though it doesn't really matter. My question is - can I combine these two queries into one query? It ...
12
votes
1answer
15k views

Optimal way to ignore duplicate inserts?

Background This problem relates to ignoring duplicate inserts using PostgreSQL 9.2 or greater. The reason I ask is because of this code: -- Ignores duplicates. INSERT INTO db_table ...
5
votes
1answer
1k views

How can I correctly choose maximum number of occurrences of a string while grouping by another field?

I am using Postgresql 9.0. I have the following fields in a table: id, name. id name 1 John 1 Mary 1 Mary 1 Mary 1 John 1 Mary 3 Paul 3 Paul 3 George ...
5
votes
2answers
3k views

Problems connecting to Windows-based Postgresql

Other computers are having trouble even finding the running Postgresql 9.1 on my Windows 7 machine. Local connections work just fine of course. In my pg_hba.conf I have: #office subnets host all ...
3
votes
0answers
186 views

How does Postgres Plus Cloud Database on AWS compare with the alternatives [closed]

I'm familiar with the difficulties of scaling regular postgres on AWS and the limitation of services like RDS. Postgres plus cloud database (bit of a mouthful) seems to run out of the box on AWS, ...
0
votes
1answer
559 views

Retrieving a fixed range of rows from Postgresql

I have a query: select qid,ansid,ans from table1 where askerid='something' This query can retrieve many rows. But I want to display only 50 rows at a time and then if the user clicks on more, the ...
0
votes
1answer
124 views

Troubles with creating initial PostgreSQL base

I'm trying to create a new base /usr/pgsql/bin/initdb -D /var/lib/pgsql/data --locale=en_USand got the following: creating template1 database in /var/lib/pgsql/data/base/1 ... FATAL: could not ...
5
votes
1answer
152 views

Rebuild a table from history records

A small disaster happened to me on my PostgreSQL 8.4 database. I follow visitors to my webpage in two tables: History +---------------+-------------+ | whereseenlast | varchar(50) | | uniqid ...
-1
votes
1answer
1k views

Postgresql could not find pg_xlog [closed]

I'm trying to mount the WAL to a different disk. I stopped the server, and did the following: (I'm using Fabric) # Move pg_xlog to wals/path.dev/ dest_dir = "/home/ec2-user/%s/wals/%s" % ...
0
votes
2answers
396 views

Merging multiple databases as separate schemas in PostgreSQL

I have to combine data from 4 different databases. Each having its own design. What i want to do is to Create a new database with my desired schema. and one schema per (to be merged) database Then ...
0
votes
1answer
1k views

Postgres Incremental Backup (Continuous Archiving WAL)

I need to take incremental backup of Postgres DB. I need to capture (backup of) daily changes on a database. So that I can restore DB with any particular data I want. As per Continuous Archiving, I ...
12
votes
1answer
2k views

What is the modern way to partition PostgreSQL across machines, when the data is “naturally partitionable”

After several years of dwelling into the "NoSQL" space, now I have a problem that is quite "relational" in its nature. Today I see data stores with quite different eyes than before. Things like Riak ...
2
votes
1answer
870 views

In Postgres, how do I cast character varying to an enum type?

I have a Postgres 8.4 table with columns that are of type "character varying", but I want to convert them to an enum type. When trying to alter the table ALTER TABLE logs ALTER COLUMN interface_type ...
3
votes
1answer
503 views

Performance impact of setting PostgreSQL to log all statements

PostgreSQL 9.2.2 on Windows 7 64-bit. The setting log_statement in the postgresql.conf file specifies what statements to log (none, DDL, data-modifying, or all). To what extent does setting ...
3
votes
1answer
360 views

pg_dump freezes on windows service application

I have a system running over Tomcat which executes a backup batch job every day, it calls pg_dump.exe from Java and stores its output to an SQL file. When I'm executing Tomcat from the shell, it ...
0
votes
1answer
241 views

Use data in a tablespace to populate another postgres database instance without actually inserting

I have a postgres database (version 8.4) with many tables. Some tables are huge in size, but the data is static (common for any instance). I have stored such tables in a separate tablespace. Is there ...
1
vote
1answer
265 views

Depending on Foreign Key Constraints to avoid deletion

In PostgreSQL, I have a 2 tables with father/parent relationship. I wish to delete the father record if no records of child are associated with it (using after triggers) Currently, I have been doing ...
1
vote
1answer
687 views

Inherit audit columns and triggers

Background I have a database with dozens of tables, some of which should have audit columns. Problem I'd like to avoid the drudgery of creating audit columns manually, and write the update and ...
1
vote
1answer
2k views

How do I list all tables in all schemas owned by the current user in Postgresql?

I can list all tables in all schemas using > \dt *.* but that also lists system tables that greatly outnumber my tables that I care about. I'd like all the tables (and possibly views) created by ...
5
votes
1answer
220 views

Determining which isolation level is appropriate

This is a homework question. For the following transactions state the isolation level that will maximize throughput without lowering the integrity of the database. Explain the answer. ...
0
votes
1answer
203 views

Can't connect to a PG database from MicroOLAP, but can connect from SSH?

I'm able to connect to my PGSQL database from SSHv2. But when I try to do the same thing on MicroOLAP Database Designer, I get this message: (Note: From my college PC, I can connect. But I can't ...
4
votes
2answers
730 views

How do I set and get custom database variables?

Using PGAdmin III I can right-click on a database, navigate to the Variables tab, and put a variable name-value property on the database itself. Is there a way to customize these? I saw an ...
3
votes
1answer
1k views

Why/how did the behavior of extract(epoch…) from “timestamp without time zone” fields change between PostgreSQL 8.4 and 9.2?

Context: We currently run PostgreSQL 8.4 in production, and are testing 9.2 internally for a future upgrade. Some issues have emerged regarding dates. The servers that run 8.4 versus 9.2 are ...
6
votes
1answer
3k views

How to determine the collation of a table in PostgreSQL?

I want to script a check of the collations used on my tables in PostgreSQL, but googling for Postgresql detect collation is not working well for me, and the documentation is not making this an easy ...
1
vote
2answers
556 views

list tables that are missing primary/foreign key in postgresql database

I've seen a ton of queries to list primary and foreign keys but how can I query for tables missing primary and foreign keys? PostgreSQL 8.4 and 9.x versions
3
votes
1answer
2k views

query to identify all data types used in postgresql database tables

I don't want to know all data types, just all data types used in my database. Can this information be queried? PostgreSQL 8.4 and 9.x versions I currently need to know all data types for over 200 ...