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 ...
2
votes
2answers
793 views
Most efficient way to add a serial column to a huge table
What's the fastest way to add a BIGSERIAL column to a huge table (~3 Bil. rows, ~ 174Gb)?
EDIT:
I want the column to be incremented values for existing rows (NOT NULL).
I didn't set a fillfactor ...
0
votes
1answer
229 views
Two-way encryption in PostgreSQL version 9.1 database
What is the process of two-way encryption of data in PostgreSQL database version 9.1 using built-in functions?
2
votes
2answers
110 views
Postgresql: Two concurrent COPY FROM
I'm importing several gigantic csv files into a table - so I'm wondering what would happen if I ran COPY FROM jobs concurrently?
5
votes
1answer
228 views
How to customize constraint violation messages?
I want define my own message for each constraint. Is it possible to customize constraint violation messages in PostgreSQL?
4
votes
1answer
138 views
PostrgeSQL 9.1 Certificate Authentication: how to get certificate common name of client?
I am using Certificate Authentication for clients on a PostgreSQL 9.1 database server. Several certificate common names are mapped to a single database user in pg_ident.conf.
I do not want to create ...
0
votes
1answer
164 views
Salvaging data from corrupt tables PostgreSQL
Am in the process of salvaging very crucial data from a corrupted database.
Am getting the following error from a very crucial table
ERROR: invalid page header in block 94206 of relation ...
2
votes
3answers
526 views
Replicating only significant records to specific database
I have a remote database which holds data for various databases in various locations. I want to do database replication, but only data specific to a certain database should be replicated to it. I can ...
3
votes
1answer
784 views
Why is my postmaster process (sometimes) becoming unmanageable after a WAL base restore?
TL;DR: Un-stoppable, unusable postmaster is being spawned when Postgres is started right after restoring its data directory from a WAL base backup. Why?
Context:
We run postgresql 8.4, on CentOS 6, ...
2
votes
1answer
373 views
Postgres - Create table with foreign key constraint too slow
I have a table called "Account" that is a heavily used table, has 17 columns and has upwards of 300,000 rows. I am trying to create a new table, "NewTable", that has a foreign key constraint to ...
4
votes
1answer
288 views
PostgreSQL data type text vs varchar without length
In PostgreSQL you can create a column with data type character varying (without length precision) or text like this:
ALTER TABLE test ADD COLUMN c1 varchar;
ALTER TABLE test ADD COLUMN c2 text;
Is ...
6
votes
2answers
290 views
PostgreSQL PITR restore
I have a PostgreSQL 9.1 server running on a CentOS 5.8 32bit OS and I have enabled WAL archiving. On this server, there are three databases: databaseA, databaseB and databaseC. Someone deletes a ...
2
votes
1answer
394 views
Possible to keep max number of records in postgresql?
Basically part of our Postgresql table is used to keep server access logs, and as such sometimes during production this can get pretty large. is there any way of setting in postgresql to have a ...
2
votes
2answers
299 views
How do I set the external editor for PostgreSQL's psql client?
What do I put in ~/.psqlrc file to set the default editor?
The editor I want to use (Textmate) is at /Users/username/bin/mate.
I found the place in the docs where they talk about it, and tried ...
0
votes
0answers
64 views
How does indexing work in postgresql? [closed]
I want to know how indexing works in postgresql? Is it something like hash-map? Can indexing be a drawback in any situation?
2
votes
2answers
134 views
Why is postgresql still consuming space on my other drive?
I am running postgreSQL 8.4 on Ubuntu 10.0.4.
I recently needed to import a large amount of data (approx 300G) into a database. I purchased an additional drive, prepared it for use on my machine, and ...
0
votes
1answer
62 views
Tightly coupling tables in postgresql
Our web-application is something like - users can create groups(request for membership of a group also), ask questions, answer etc. It is still in the development stage. What I want to do is tightly ...
0
votes
2answers
1k views
How to configure postgresql to use multiple cores?
How can I configure postgresql to use multiple cores in a single machine , is it automatically uses it ?
2
votes
1answer
220 views
Using pgsql_fdw in a function result “ERROR: cache lookup failed for type 0”
Now we want to use pgsql_fdw to select a table of remote postgresql database，When we
select the table in a session it is okey ,but when we use the foreign table in a funciton
it turns out "ERROR: ...
4
votes
2answers
3k views
Easy Postgresql master/slave replication with automatic failover on Linux?
I need a reliable SQL DB for our application and have been looking at Postgresql's streaming replication support. It seems that it's not hard to setup master/slave replication and you can use the ...
1
vote
1answer
114 views
PostgreSQL Replication for small, low-usage database
I have PostgreSQL 8.4 installed on a headless Ubuntu 10.04 server. Sometimes the server is not available. I would like to continue working on the database, so I thought of installing PostgreSQL onto ...
2
votes
2answers
842 views
Trouble recovering data from corrupted PostgreSQL tables
I'm trying to recover my database after a recent corruption of the same. Now I have two tables that are really giving me trouble.
When I do select count(*) One table returns:
ERROR: could not ...
2
votes
2answers
192 views
Attacks on Postgresql listening for requests on 'localhost'
I am using jboss server for my servlets and the postgresql as the database server, listening on port 5432 on localhost. My jboss server is bound to a public IP. I want to know that since my postgesql ...
1
vote
1answer
454 views
Really slow DISTINCT ON query with multiple joins
Originally posted:
http://stackoverflow.com/questions/11173717/expensive-query-on-select-distinct-with-multiple-inner-join-in-postgres
The songs table has only about 4k rows, posts and stations have ...
3
votes
2answers
778 views
Postgres primary key sorted in the reverse order. Will it use the index efficiently?
My Rails database is backed by Postgres database. As you may know, each table in Rails gets assigned a primary key which is of Integer type and is indexed.
My list views show records in the reverse ...
1
vote
1answer
392 views
Syncing two (or more) PostgreSQL 9.1 clusters
This question assumes the use of PostgreSQL 9.1.
Say I have two PostgreSQL database clusters. Each cluster contains three PostgreSQL servers. A master, a slave synced via synchronous replication and ...
1
vote
1answer
289 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
3
votes
2answers
301 views
Slow ORDER BY with LIMIT
I have this query:
SELECT *
FROM location
WHERE to_tsvector('simple',unaccent2("city"))
@@ to_tsquery('simple',unaccent2('wroclaw'))
order by displaycount
I'm happy with it:
"Sort ...
1
vote
1answer
126 views
PG_bulkload utility postgresql need help
i am using COPY command to export the data to the out file with changes made to the data before export using option ST_asBinary() using command
COPY (
SELECT st_asBinary(geom) FROM n_america
) TO ...
0
votes
1answer
250 views
FATAL: terminating autovacuum process due to administrator command [closed]
I am running postgresql 8.4 on Ubuntu 10.0.4. I was in the process of creating schemas/importing data when I came across some error messages. Here is a snippet of the output at the console:
CREATE ...
0
votes
1answer
210 views
Unable to reset the password of users in postgres
Evrytime i run, su - postgres it asks me for a password which I did not set. I simply installed the postgres. I have gone to the pg_hba.conf.
# Database administrative login by Unix domain socket
...
4
votes
1answer
1k views
How to set Postgresql database to see date as “MDY” permanently
Ho can I set my database to see the 'date' as "MDY" without running:
SET datestyle = "ISO, MDY";
every-time I'm trying to access it?
I'm using Postgresql version 9.1, Ubuntu 12.04.
My system ...
3
votes
2answers
7k views
How do I reset the postgres password for PostgreSQL on Windows?
I have installed PostgreSQL on my Windows machine for development. Now I haven't used it for a while, and have forgotten the password for user postgres (default admin user in PostgreSQL). How can I ...
2
votes
2answers
805 views
How to create column in DB with default value random string
Can I create column in DB table (PostgreSQL) which have default value random string, and how ?
If is not possible, please let me know that.
0
votes
1answer
132 views
What database do most mobile start ups use? [closed]
mongodb?
MySQL?
PostgreSQL?
If someone would add pluses and minus, let me know.
Why I ask:
I am a mobile startup. I need a database that supports spatial indexes. The spatial index must be able to ...
3
votes
1answer
3k views
How do I copy databases from Postgres 8.4 to Postgres 9.1?
My trac server recently started having issues. It was running a very outdated version of Ubuntu, and I was unable to properly use apt-get to correct the issues I was having. So instead I backed up the ...
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 ...
3
votes
2answers
814 views
Tool for Reverse Engineering of PostgreSQL Database on Linux (Ubuntu)
Please suggest a tool to reverse engineer a PostgreSQL DB into an entity-relationship-diagram (ERD) or equivalent ??
Ideally, I'm looking for a simple ERD or UML class diagram in a format that is ...
4
votes
1answer
144 views
timelines and history files PostgreSQL 9.1
What is the purpose of timelines, history files, and the setting 'recovery_target_timeline' in recovery.conf?
The vague understanding I have from the PostgreSQL 9.1 documentation is when the slave ...
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 ...
5
votes
1answer
204 views
Database Exceptions with Jetty 8.1.0 with BTM
We are running Jetty 8.1.0 with BTM 2.1.2.
We are using Postgres as our DB.
Everything works great except that when we run any query with a condition where sometable's primary=null, the server loses ...
1
vote
1answer
505 views
postgres composite index design
In my rails application using the Postgres database, I have a table called tw_schedules which belongs to a scenarios table (one scenario to many tw_schedules). In addition to the scenario_id foreign ...
1
vote
3answers
985 views
Passing arguments to psql
I'm running a plpgsql script in Postgres 8.3 - I would like to pass arguments to this script via psql. I'm currently executing the script like:
psql -d database -u user -f update_file.sql
I came ...
1
vote
1answer
179 views
What is the “Data partitioned, Shared-Nothing, Parallel Database” solution for PostgreSQL ?
I was looking for a open source parallel database solution. It seems Postgres-XC is a viable solution. However, I don't need the replication. In this case, is there any better solution towards read ...
3
votes
1answer
253 views
is my postgresql-server thread-safe?
before 9.0 i had to compile my postgresql-server with "--enable-thread-safety" for http://www.bacula.org
since 9.0 the config-paramater is renamed to "--disable-thread-safety".
The "PostgreSQL ...
0
votes
1answer
207 views
Can't get past password lock on fresh install of postgreSQL
I feel really dumb writing this, I wanted to have a mess around with databases so I just installed postgreSQL (9.1)
During the installation I am asked to provide a superuser password. I provide a ...
1
vote
1answer
840 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 ...
2
votes
2answers
443 views
Streaming Replication in PostgreSQL
I'm trying to set up two PostgreSQL servers on one machine and perform a streaming replication. I have succeeded once, but when i've tried again following exactly the same steps it doesn't work.. ...
3
votes
2answers
426 views
Do all users need access to the postgres database in pg_hba.conf?
I was just testing the user creation process in pgAdmin. I have a database db1 for which I want to create a user to be able to connect to it. I want this to be the only database the user can connect ...
2
votes
2answers
549 views
Importing LARGE quantity of data into a PostgreSQL database
I am importing a large amount of data into a PostgreSQL server (across multiple databases on the server).
When I last attempted to import the data, it effectively "crashed" my machine (Ubuntu ...
9
votes
1answer
268 views
Disable explicit commits in JDBC, detect them in SQL, or put the database in a readonly state
Background: I'm working on http://sqlfiddle.com (my site), and am trying to prevent one avenue of abuse possible there. I'm hoping that by asking about a problem I'm currently addressing, I don't ...
