Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.

Filter by
Sorted by
Tagged with
6
votes
1answer
500 views

in postgres, can one optimize a table across partitions?

in a database with many companies, segregating them using schema and search_path almost all access comes at the company level, that is, limited to a single schema. some data-warehouse and accounting ...
2
votes
1answer
2k views

hstore for versioning fields in postgresql

I have a postgresql database that will hold about 50 tables, each of them having about 15 fields, it would have at least 300.000 rows on each table. In order to track the changes done on each field I ...
15
votes
5answers
45k views

PostgreSQL: The database cluster initialization failed

Both C:\PostgreSQL and C:\PostgreSQL\data have postgres user with full access and admin rights. I ran postgresql-9.1.2-1-windows.exe from postgres user as admin. Target C:\PostgreSQL Every way I try,...
30
votes
3answers
33k views

Configuring PostgreSQL for write performance

One of my PostgreSQL servers hosts several (1-3) databases which receive a constant stream of data. The data is not particularly structured, it amounts to the current time and a variety of observed ...
2
votes
1answer
4k views

How to make it impossible for a postgres user to delete databases?

What yould be the sql statement (that the user postgres will execute) so it will be impossible for the postgres user user1 to delete (drop) databases? Or can I add a rule into some config file?
14
votes
3answers
11k views

How to convert mysql to postgresql? [closed]

I am desperately looking for a conversion tool to convert a big mysql database to postgresql. I need the tool/script to be: Free Works under Linux Simple to use and not buggy You actually tried and ...
5
votes
1answer
840 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
3k views

Postgresql 9.1 replication delay

I just set up a PostgreSQL 9.1.2 database server on debian squeeze. I configured a standby server (hot standby) on a second identical server (squeeze, PG 9.1.2). On the master I set these parameters (...
1
vote
1answer
412 views

Using PL/PerlU with a non system perl lib

psql -1f check_email.pl.sql ccushing postgres SET psql:check_email.pl.sql:7: ERROR: Can't locate Email/Valid.pm in @INC (@INC contains: /usr/lib/perl5/site_perl /usr/share/perl5/site_perl /usr/lib/...
12
votes
4answers
14k views

No NULLs, yet invalid byte sequence for encoding “UTF8”: 0x00

I've spent the last 8 hours trying to import the output of 'mysqldump --compatible=postgresql' into PostgreSQL 8.4.9, and I've read at least 20 different threads here and elesewhere already about this ...
7
votes
2answers
12k views

PostgreSQL query with max and min date plus associated id per row

I have the following table: CREATE TABLE trans ( id SERIAL PRIMARY KEY, trans_date date, trans_time time ); I want to have the following view CREATE OR REPLACE VIEW daily_trans ...
0
votes
0answers
48 views

postgresql view [duplicate]

Possible Duplicate: postgresql view with max min with id I have the following table: CREATE TABLE trans ( id SERIAL PRIMARY KEY, trans_date date, trans_time time ); I want ...
3
votes
1answer
2k views

Any Idea how to migrate from PostgresSQL 8.4 to PostgresSQL 9.1.1 in opensuse12.1

I had a crashed opensuse 11.3 that's why I installed opensuse 12.1.. after I installed (via yast) and edited /etc/passwd I do this: rcpostgresql start and this shows on the /var/log/messeges ~ # ...
93
votes
3answers
90k views

PostgreSQL multi-column unique constraint and NULL values

I have a table like the following: create table my_table ( id int8 not null, id_A int8 not null, id_B int8 not null, id_C int8 null, constraint pk_my_table primary key (id), ...
13
votes
5answers
11k views

Is there a means to set the owner of all objects in a PostgreSQL database at the same time?

https://stackoverflow.com/questions/1348126/modify-owner-on-all-tables-simultaneously-in-postgresql describes some nifty ways to change table and other objects to a specific user, and it works ...
6
votes
2answers
16k views

PostgreSQL: Query for location of global tablespace?

I have been trying to make our database clients pro-active about not filling up the partition on which the database they are using resides. As all our clients are on the same host as the database ...
18
votes
3answers
28k views

Automating failover in PostgreSQL 9.1

How does one setup two identical servers for automatic failover in PostgreSQL 9.1. OS Centos 5 PostgreSQL 9.1 compiled from source The postgres user account exists on both machines and has a ...
1
vote
1answer
2k views

64 bit Postgres 9.1 Limitions on Windows 2008 R2?

When Deploying Postgres on Windows Server 2008 R2: Are there any limitations on windows that don't exist on Linux? Does the postgres scale in the same way on windows as it does on Linux? Is ...
1
vote
3answers
302 views

PostgreSQL and query planner

I have question about query planner in PostgreSQL. I know, that this planner is taking data from pg_statistics, but... Can anyone tell me on what basis this planner is taking those informations to ...
8
votes
3answers
12k views

PostgreSQL vs. Oracle High Availability solutions?

PostgreSQL has a matrix of different high availability options which represent many different ways of building replication into an RDBMS. Here is the PostgreSQL High Availability, Load Balancing, ...
2
votes
3answers
690 views

Replication chaining with PostgreSQL

We have an unusual replication scenario and I am not sure which tool would be appropriate for that. Basically, we have a legacy non-normalized database that we want to refactor into a normalized ...
4
votes
1answer
3k views

Postgres uuid: Use as primary key, or in addition to SERIAL -for disconnected app-

For a new SASS app with postgres 9, that will support disconnected clients. The client not will use the regular IDs, only the UUID. The clients will be on iOS, html5/python and perhaps later on other ...
3
votes
2answers
1k views

How do I configure PostgreSQL 8.3 to log bind parameters?

I'm running into some unique constraint problems with my database. I can't reproduce them, but see error messages from our production system: exception 'UniqueViolation' with message 'SQLSTATE[...
7
votes
1answer
794 views

PostgreSQL partition hierarchy design for centralized logging

I am looking into setting up logging from all of my servers into a centralized postgresql database. It makes sense to be able to drop logs by date or by host, so I'd like to set it up with ...
4
votes
2answers
2k views

VACUUM and VACUUM FULL do not return space to OS

I'm running Postgres 8.3.3. I have a postgres database on a Linux filesystem that is quite full. I've run VACUUM and VACUUM FULL, yet none of the space has been returned to the OS. When I observe ...
4
votes
1answer
821 views

Single node parallelism with PL/Proxy?

I have seen some suggest that single node parallelism is possible, but all of the material I have found so far deals with parallelizing across nodes. I am interested in getting large static queries to ...
8
votes
2answers
3k views

PostgreSQL: reuse of complex intermediate result in same query

Using PostgreSQL (8.4), I'm creating a view that summarizes various results from a few tables (e.g creating columns a, b, c in the view), and then I need to combine some of these results together in ...
1
vote
1answer
326 views

Show all variations of 2 characters in postgres

How would I do this in postgres : Lets say I have an x and a y and I want to show all possible combinations : xx yy xy yx How can I make postgres do this for me ?
6
votes
2answers
5k views

What is the purpose of PGBouncer's parameter server_reset_query?

Recently I started learning about pgbouncer, but there are some things I do not understand about the parameter server_reset_query. Question 1: What is the main purpose of server_reset_query? ...
2
votes
1answer
5k views

PostgreSQL replication: out-of-sequence timeline ID, when former master made slave

I've configured master slave replication in PostgreSQL, following this tutorial: http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial#Binary_Replication_in_6_Steps It worked fine. Now I've ...
6
votes
1answer
4k views

Does my choice of pg_dump format impact restore speed?

According to the docs for my version of Postgres (8.4.2), pg_dump can "output in script or archive file formats." The script-formatted dump (4GB) takes 1.5 hours to restore, and I'm wondering if the ...
7
votes
1answer
10k views

Type conversion with default fallback value

In PostgreSQL (8.4), I'm trying to convert a string parameter into a date within a SQL query, falling back to now() when the string is not a valid date (or empty). In "pseudo-SQL", this would be ...
13
votes
2answers
17k views

How to promote an existing index to primary key in PostgreSQL

I know how to make a primary key within a table, but how do I make an existing index a primary key? I'm trying to copy an existing table from one database to another. When I show the table, the index ...
3
votes
1answer
1k views

Storing bitsets in PostgreSQL?

I need to calculate reach, grouped by different criterions, and would prefer to keep everything in my database. I'm looking for either a pre-built bitset implementation for PostgreSQL, or pointers on ...
2
votes
3answers
506 views

New Job and Company uses PostgreSQL [closed]

I have used MySQL and SQL server all of my professional career. The company I will be working for uses PostgreSQL. Can someone who was/is in a similar situation please give me some insight on what ...
7
votes
4answers
16k views

Visualization of MySQL/PostgreSQL table structure as a graph/UML

Is there any program, source code or API that will take a MySQL or PostgreSQL schema and spit out a UML or some other graph visualizing of it?
26
votes
2answers
13k views

Force PostgreSQL clients to use SSL?

I have configured ssl = on in postgresql.conf (and installed a certificate etcetera). Does this ensure that all clients will always connect over SSL? (I.e. does ssl = on it make it impossible to ...
3
votes
2answers
1k views

Role problem of PostgreSQL

I want to create a role named cp with some defined privileges, then we will create some other roles which will be granted with cp role. I know Oracle can do this job. For examle grant resources to ...
3
votes
2answers
6k views

Reference a non primary key in another table in postgresql

I have a two columns should references two columns in another table. And those two columns are not primary key. I think I can't use foreign key, because it can only references primary keys.
2
votes
1answer
565 views

How to perform asynchronous cascade slave replication of PostgreSQL database?

This is a feasibility/architectural question (I am no database administrator). Let's imagine a PostgreSQL database running online (like those from Heroku for example). One would like to maintain a ...
1
vote
1answer
1k views

PostgreSQL 8.4 hangs on create table, reindex

I have a rather small database(200Mb) which was running on ubuntu 10.10/amazon ec2 fine for a year almost but today I ran into the following problem: When I create a table that references other table ...
3
votes
3answers
606 views

Can I limit access to postgresql 8.4 databases via ODBC on the server side?

I have a bit of a tricky problem at the moment that I could really use some help to solve. I'll do my best to explain it but please feel free to ask questions :-) We have a postgresql 8.4 database ...
12
votes
3answers
27k views

SQL to read XML from file into PostgreSQL database

How can I write SQL to read an XML file into a PostgreSQL XML value? PostgreSQL has a native XML data type with the XMLPARSE function to parse a text string to that type. It also has ways to read ...
44
votes
4answers
31k views

Function Performance

Coming from a MySQL background, where stored procedure performance (older article) and usability are questionable, I am evaluating PostgreSQL for a new product for my company. One of the things I ...
3
votes
1answer
7k views

postgres and passwordless authentication

We have users for out postgres database and none have passwords... Do not know the history behind it. When I try to use clients to connect to these databases, for instance, pgadmin III, the ...
9
votes
1answer
104 views

How can I see a query after rules have been applied?

From docs - 37.3.1.1. "A First Rule Step by Step" CREATE TABLE shoelace_log ( sl_name text, -- shoelace changed sl_avail integer, -- new available value log_who ...
5
votes
1answer
1k views

Load Balancing / MPP with Postgres 9.1

I'm trying to setup a testbed for bigdata analytics (basically around 2 to 3 TB base, slight write [only feeding the db], but possibly read intensive queries for analytics purposes). Since I'm ...
7
votes
2answers
4k views

Is DB2 more reliable and stable compared to PostgreSQL or MySQL?

Always i was using MySQL or PostgreSQL for my projects such as Web/Desktop apps. I had data loss few times and thanks to old backup i was always up and running. But for a recent project for Airport, ...
3
votes
1answer
805 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, ...
20
votes
2answers
56k views

PgAdmin III - How to connect to database when password is empty?

I have installed PostgreSQL 9.1 on my PC (Win 7). I have a small Java application connecting successfully to it with login=sa and password="". The connection works. However, it is refused from ...