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 ...
1
vote
1answer
139 views
TimeZone parameter in postgresql.conf
postgresql documentation says:
All timezone-aware dates and times are stored internally in UTC. They
are converted to local time in the zone specified by the timezone
configuration parameter ...
1
vote
1answer
319 views
Is PostgreSQL 9.2 autovacuum sufficient for a busy table?
I have one table on a PostgreSQL 9.2/PostGIS 2.0.1 database:
CREATE TABLE ch02.markers
(
ff_id SERIAL PRIMARY KEY,
col1 boolean,
col2 smallint,
created_at timestamp,
lat double precision,
...
1
vote
1answer
286 views
Search for null in id column over all tables programmatically
I want to automatically check for null ID's over all id columns in my database, I have to check all tables of which there is about 50,000 so manually doing this is infeasible
1
vote
3answers
2k views
Starting postgresql service and specifying data directory
I have recently installed pg 8.4 on Ubuntu 10.0.4 using apt-get install.
It installed pg as a service which starts ehenever the machine starts. However I have created a db cluster in a specific ...
1
vote
2answers
149 views
Perf of query of large table by date?
I have a table with some sensor data in it which grows every minute with a new measurement for that specific sensor. Something like:
entry_id (pk), sensor_id (fk), measurement_value, created_at
I ...
1
vote
1answer
828 views
Freeing disk space after dropped database
I'm working on a dev system, and I have been restoring to a database, say "foo", that I'm using for dev purposes. As I'm working through the kinks, I've just been running DROP DATABASE foo. However, I ...
1
vote
1answer
111 views
How to know there is a maintenance job running on a postgresql database
I have a python script on postgresql database which is fetching records from sql server database and inserting it to postgresql DB after some filtering. this jobs is running by cron every two minutes. ...
1
vote
3answers
1k views
How to convert a MySQL database to PostgreSQL?
Is there any command line tool for converting the MySQL database to PostgreSQL? I'd also like to know if there is a way to convert the database using the normal mysqldump command.
1
vote
1answer
135 views
Working of window functions and idea window size for window function
I am not able to understand the concept of window functions. How exactly they work and what are the pros and cons of such technique?
I have read that using limit and offset is slow but its still ...
1
vote
2answers
375 views
pg_upgrade fails with an unspecified error
I’m running Postgres 9.1’s pg_ugrade to migrate from 9.0 to 9.1 on Windows x64. After editing the permissions to allow full trust, running the tool as the postgres user, and working around a bug ...
1
vote
3answers
244 views
access columns returned by psql from bash
I want to use value returned by psql in bash. This is the script:
psql "connection parameters" -c "SELECT pg_database_size('dbname');"
The output is like this:
pg_database_size
...
1
vote
1answer
437 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 ...
1
vote
1answer
426 views
storing a mirror of IMAP/pop3 folder/inbox in a local DB, what choices are there?
I have a email inbox that will soon hit 4 million messages, I have an app that runs different policies over the inbox to associate emails with each other much like the conversation plugin for ...
1
vote
1answer
487 views
About “pg_ctl promote” of PostgreSQL9.1
Today I test the new feature about "replication features" of PostgreSQL9.1,
First I have installed the Hot-standby of PostgreSQL9.1 successfully. It is not a problem。
What I want to test is that I ...
1
vote
2answers
141 views
Postgresql Cost-Based Vacuum Delay parameters
How to set the these parameters on postgresql.conf ?
What I should consider while setting them?
What should I monitor?
#vacuum_cost_delay = 0ms
#vacuum_cost_page_hit = 1 ...
1
vote
1answer
5k views
How do I specify that a column should be auto-incremented in pgAdmin?
I have started to learn pgAdmin III to manage a PostgreSQL database. But it wasn't an easy to use application.
If I create or have created a table with pgAdmin III, how can I add ...
1
vote
1answer
14 views
Big jump in search time for Postgres Index query for results with high selectivity
I am doing some performance comparison of databases and lucene for full-text searching.
So I use Postgres to create an Index for the data to search:
CREATE INDEX bodies_index
ON bodies
USING gin
...
1
vote
2answers
26 views
Why PostreSQL 9.2 does not use index scan if it can?
My PosgreSQL planner (9.2) does not pick index and rather choose to do seq scan.
Here is my table. The index I am talking about is name_left_prefix
cwu=# \d web_city;
...
1
vote
1answer
69 views
Get all partition names for a table
I want to list all the Partitions created by dynamic triggers in Postgresql 9.1
I was able to generate a count of partitions for tables which are partitioned using Frank Heikens' answer in ...
1
vote
1answer
23 views
How to get user-specific settings in PostgreSQL?
I have to collect some information of PostgreSQL roles. One piece of information is whether the role has different settings than 'usual', usual meaning here default values for only a few parameters ...
1
vote
1answer
49 views
Equivalent of MRG_MYISAM in databases other than MySQL? [closed]
Does anyone know if other database have something equivalent to MRG_MYISAM (aka the MERGE table type/storage engine)?
I know about fragmenting, but this is not quite the same AFAIK. We're using ...
1
vote
1answer
31 views
Drop database from remote that has connected users
I have a "small" problem. There is a database server that contains a database where some test frontends are connected to. I want to update that database from a dump + some scripts to the most recent ...
1
vote
1answer
24 views
Hiding variable input in psql
I am attempting to prompt for a username and password when running a database creation script, and I want to hide any text input for my password prompt. In Oracle's sqlplus, I can use HIDE at the end ...
1
vote
1answer
46 views
Why does pgAdmin3 set OIDS=FALSE and owner to postgres by default?
Why does pgAdmin3 automatically set the table owner to postgres when its created and set OIDS=FALSE? I am new to PostgreSQL and want to know the rational behind this default behavior.
-- Table: ...
1
vote
1answer
109 views
Monitoring real-time statistics on PostgreSQL
I need to monitor the DB performance on a distributed system with multiple databases. Some of the database servers use pgbouncer to pool connections and in those servers I can connect to pgbouncer and ...
1
vote
1answer
103 views
Postgresql query speed
This is my first time seriously attempting to get into the guts of the speed of a query on Postgres, and I was hoping for some more explanation of exactly what is going on. I am using Postgres v 9.1 ...
1
vote
1answer
44 views
clusterdb stopped working for PostgreSQL database in Ubuntu 12.04 server
After a fresh PostgreSQL install (version 9.2) on my Ubuntu server 12.04, clusterdb utility stopped working. I used the same config files as I used with version 9.1.
Below is the actual command:
...
1
vote
1answer
147 views
Very slow DELETE in PostgreSQL, workaround?
I have a database on PostgreSQL 9.2 that has a main schema with around 70 tables and a variable number of identically structured per-client schemas of 30 tables each. The client schemas have foreign ...
1
vote
1answer
44 views
Script to get duration
I am new to PostgreSQL. I am trying to write a query which can give me duration of the time. The fields are in the format yyyymmddhhmmss. In between we will get empty fields for Start_Time or ...
1
vote
1answer
61 views
Postgres copy data with \xYY as plain string instead of interpreting as encoded string
I have a log file full of URLs, generated by Bro IDS.
When Bro logs a URL with non-ascii characters in it, it inserts \xYY where YY is the hexadecimal character code. Also, some URLs contain "\x".
...
1
vote
1answer
60 views
How to view the current settings of Autovacuum in Postgres?
I've found a million examples of how to set Autovacuum's configuration settings, but can't seem to find how to list the current configuration.
Postgres 9.1 is the version I'm most interested in.
1
vote
2answers
54 views
Dumping and restoring on db server - postgres
Is there a way to execute a backup and restore of PostgreSQL DB from App server without calling pg_dump from App server and executing pg_restore also on App server. In this way I'm executing ...
1
vote
1answer
59 views
Single column join vs multicolumn join
In a test with two schemas that conceptually contain the same data I got a bigger performance difference then expected.
In one scheme a surrogate key is used which means that in the join condition ...
1
vote
1answer
74 views
Postgresql Two-phase Commit Transaction Identifier
Is it possible to perform a two-phase commit in Postgresql when the databases exist on the same server?
As a simple test, I would connect to the first database and run:
BEGIN;
PREPARE TRANSACTION ...
1
vote
1answer
46 views
INSERT trigger dying with with #target columns not equal to #expressions, but they are equal
I am trying to write my first trigger. It is an insert trigger as follows:
<b>Fatal error</b>: postgres7 error: [-1: ERROR: INSERT has more target columns than expressions at character ...
1
vote
1answer
82 views
Postgres: Can the archive command for master and standby point to the same directory?
I use streaming replication and PITR (WAL files) in my cluster, currently I have different versions of postgresql.conf for the master and the standby server.
The only difference in the files is the ...
1
vote
1answer
85 views
PostgreSQL How do I convert struct text to plain *char in C functions?
I'm using my internal C function which doesn't know about postgresql's text struct, how do I pass text argument when char * expected?
#include <stdio.h>
#include <string.h>
#include ...
1
vote
1answer
191 views
Lost postgreSQL password in Windows installation
I have postgresql 9.2 installed on a windows XP machine. I have lost the postgres password.
I looked at answers given to a similar question asked 6 months ago. The answer given there required the ...
1
vote
2answers
282 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 ...
1
vote
2answers
1k 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 ...
1
vote
1answer
229 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
113 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.
1
vote
1answer
195 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
80 views
What is the purpose of session pool_mode in pgbouncer?
Since in this mode pgbouncer creates outbound connection for each inbound connection it seems to me that pgbouncer doesn't work as pooler.
It's just 1 to 1 mapping.
Where am I wrong?
1
vote
1answer
302 views
Permission denied in file trying to import
When I try to use \i on a file that is not in the psql.exe folder it says C:: permission denied. For example I have a file with SQL command at C:\Users\Work\Desktop\School Work\load_database.sql and ...
1
vote
2answers
49 views
PostgreSQL custom domain with trigger
Is it possible to create custom domain / data type and associate some trigger with it, for example hash password?
I will have for example password domain and if I set some value to this column, it ...
1
vote
1answer
73 views
Postgres not vacuuming 1 table
I have created a table where I store serialized Java objects. They are frequently accessed and costly to create, so that is the reason. Solution works fine to me, however table size grows to 25GB ...
1
vote
1answer
175 views
How to check if databases are consistent in pgsql replication?
There is some tool for mysql (from percona I think) which could compare databases by making md5 sums of tables data. We used it in our mysql replication system to check if data is correctly ...
1
vote
1answer
200 views
drop postgres table with minimal locking
I'm trying to run the following Postgres migration script
DROP TABLE some_table;
DROP TABLE another_table;
The problem is that this script causes excessive locking when run on the production ...
1
vote
1answer
241 views
PostgreSQL - How can I manage WAL archiving for different databases on a server?
I'd like to use WAL archiving for incremental backup of my database. But I have 2 databases on my server, and I can't afford the disc space for backup of both of them. So I only want to backup one of ...
