Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.

Filter by
Sorted by
Tagged with
3
votes
2answers
5k views

What is the best way to automatically create a snapshot of the table at the certain time?

I have running and quite loaded PostgreSQL server. I need to take snapshots of the certain tables at the certain time (midnight). Those tables are pretty loaded (a lot of updates and inserts). What ...
2
votes
1answer
2k 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: ...
12
votes
1answer
2k 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 ...
11
votes
2answers
11k 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 ...
0
votes
1answer
135 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 ...
2
votes
2answers
566 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
192 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 ...
4
votes
2answers
7k 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
1answer
2k 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 ...
2
votes
1answer
9k views

Really slow DISTINCT ON query with multiple joins

Originally posted: https://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 ...
11
votes
2answers
4k 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 (cost=3842....
1
vote
1answer
2k 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 ...
-1
votes
1answer
209 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 ...
2
votes
1answer
2k 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 ...
13
votes
1answer
663 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
467 views

What is the “Data partitioned, Shared-Nothing, Parallel Database” solution for PostgreSQL ? [closed]

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 ...
1
vote
1answer
1k 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 ...
6
votes
2answers
3k 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 10.0.4),...
0
votes
1answer
204 views

Postgres Client - save table sorting

Is there a Postgres client (for Windows) that can save customized sorting when viewing tables? Obviously I could write and save a query for this, but perhaps there's a client that can do this for me. ...
3
votes
2answers
1k views

speeding up a deletion in postgresql

I came up with: drop table if exists idtemp; create temp table idtemp as select documentid from taskflag where taskid='coref' and state = 2 order by statechanged asc limit howmany; ...
2
votes
1answer
398 views

Initial recovery Postgres with backup in sync commit mode

I've got a master and a slave postgres 9.1.3 database. I would like to init the backup database for the first time. I did the following steps: Stopped master Copied all files to Slave Create recory....
3
votes
2answers
859 views

Migrating and normalizing data

We are rewriting a large Django application which uses a MySQL database, and the database schema needs to undergo major modifications. We're also porting the app to use PostgreSQL, for a couple of ...
8
votes
1answer
4k 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
2answers
3k views

Creating a test database from Production database (different machines & servers)

I'd like to set up a test database on a separate server. Production database is MS SQL Server and the test server is a *nix box so I'd like to have the test server running either PostgreSQL or MySQL. ...
8
votes
1answer
17k views

how do I execute a void-returning procedure in pgsql 9.1?

If I execute the following in a file with the pgsql command, it complains that the procedure needs to be called with 'perform'. But when I try to use perform, it tells me that perform is not defined. ...
1
vote
1answer
479 views

using COPY command and wal_buffers

since COPY command runs inside a single transaction, if its WAL data doesn't fit into wal_buffers? for example if we are bulk importing from CSV file.
3
votes
1answer
1k views

What happens when checkpoint_segments reached?

I'm a bit confused with checkpoint_segments definition in PostgreSQL documentation. What happens when checkpoint_segments reached? The dirty pages in shared_buffers written to WAL. Then what is the ...
3
votes
1answer
942 views

PostgreSQL archive_command on Windows

What is the best method for WAL log transfer on Windows? I have two servers with Windows Server 2008, PostgreSQL 9.0 64bit. Servers do not have any shared SAN disk, they are on different hosts, ...
10
votes
2answers
8k views

Can I have one table be an “alias”, or “symlink”, to another?

I have two tables with the same structure, A and B. A certain application is written so that it always writes the same data to both tables. After a discussion with a colleague about the potential to ...
2
votes
0answers
60 views

Splitting a wide table [duplicate]

Possible Duplicate: Database Design: New Table versus New Columns I'm in the process of building a DB schema that contains information about something we call "blocks" (not very important to the ...
5
votes
1answer
848 views

Database as a service (DbaaS) over dimensions--performance, scalability, & reliability?

MySql Database as a service: Amazon Xeround Another eg. PostgreSQL Database-as-a-Service. You can get the list of similar products here. Does anyone have details on their performance, reliability, ...
2
votes
1answer
8k 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 ...
5
votes
1answer
800 views

Upgrade Postgres without doing a pg_dumpall

I'm upgrading from postgres 8.4 to 9.0 and looking at the manual it looks like this is my only choice: pg_dumpall -p 5432 | psql -d postgres -p 6543 Which means I'll need to stop any writes to the ...
2
votes
1answer
458 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
330 views

How do I resolve Postgresql error, “PL/Perl function must return reference to hash or array”?

The follow code used to work in Postgresql 8.4.11 with perl v5.10.1: =# select * From testfunction(); testfunction ------------------------ http://www.google.com/ However, after doing a ...
1
vote
2answers
3k views

How to convert SQL Server .MDF database to Mac OS PostgreSQL database on Mac OS X?

I need to import a SQL Server database which is in the .mdf format into a PostgreSQL database. My system is Mac OS X. How to do it in the easiest way?
6
votes
5answers
11k views

Altering table schema takes too much time

I am using PostgreSQL database. I have a table with millions of rows. I have a varchar column with 2000 size and i want to make it to 4000. I ran the alter table command, but it is taking too much ...
1
vote
1answer
2k 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. ...
6
votes
3answers
20k views

How to connect to remote PostgreSQL server

I wrote a web app, using java + spring+ PostgreSQL, which I have up and running. I have a tester for my system and I want him to be able to connect to my database directly to check the result of ...
1
vote
1answer
223 views

Postgres table size doubles are transaction completes

I have a table with 50 million rows and 40+ text columns. Size of the table is around 17GB. Inside a transaction, I add another integer column to the table and update all the rows with corresponding ...
21
votes
1answer
14k views

Why does NOT IN with a set containing NULL always return FALSE/NULL?

I had a query (for Postgres and Informix) with a NOT IN clause containing a subquery that in some cases returned NULL values, causing that clause (and the entire query) to fail to return anything. ...
1
vote
1answer
799 views

PostgreSQL vs MySQL benchmarks when a 2nd level cache is included

Has anyone been able to find any benchmarks comparing PostgreSQL and MySQL performance when they are used by a system through Hibernate (or another comparable ORM) and the 2nd level cache (backed by ...
17
votes
2answers
15k views

Index on primary key not used in simple join

I have the following table and index definitions: CREATE TABLE munkalap ( munkalap_id serial PRIMARY KEY, ... ); CREATE TABLE munkalap_lepes ( munkalap_lepes_id serial PRIMARY KEY, ...
3
votes
1answer
915 views

querying a linked PostgreSQL database in SQL Server 2008

We will be deploying an application with a Postgres back-end and need to provide report writing capabilities in SQL Server. I am exploring options for real-time or near real-time reporting in this ...
66
votes
3answers
39k views

What are the drawbacks with using UUID or GUID as a primary key?

I would like to build a distributed system. I need to store data in databases and it would be helpful to use an UUID or a GUID as a primary key on some tables. I assume it's a drawbacks with this ...
4
votes
2answers
5k views

Improving performance of nasty nested-view joins

I have a moderate-sized database spread out over a few tables, the rough architecture is: Input Data (Data ID, Session ID and a few fields of statistical importance) Input File (Data ID and a blob) ...
9
votes
1answer
7k views

Fast general method to calculate percentiles

I want to find n>1 percentiles of an unsorted column in PostgreSQL. For example the 20th, 40th, 60th, 80th and 100th percentile. An obvious solution is to count and sort the column and then do a ...
3
votes
1answer
18k views

How to export a table on postgresql command line

Anyone can do tell me how to do that? (I know maybe is a dumb question but I'm trying to learn how to use command line of postgresql) ~$ psql mybase psql (9.1.3) Type "help" for help. and then ...
4
votes
1answer
3k views

Dropping indexes doesn't seem to release space to the OS. Can it?

I'm running PostgreSQL 9.1. I have noticed really alarmingly bad indexes are taking up a lot of space on my server, e.g: evlampts=# SELECT pg_size_pretty(pg_relation_size('mcdata_2011_07')) AS ...
5
votes
1answer
2k views

Concurrency with Select-conditional Insert/Update/Delete - PostgreSQL

I have an intervals table which stores a start date and an end date for variables variable_id (four columns : id, start, end, variable_id). Clients compute new intervals for one variable and store ...

1
264 265
266
267 268
270