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 ...
0
votes
1answer
38 views
Pattern matching and substring replacement in PostgreSQL [closed]
I would like to do pattern matching and substring replacement in PostgreSQL with a "select-case-when-then-update-query".
Example:
tableA
field1 |field2 | field3
...
0
votes
1answer
36 views
How to install and configure Postgres-XC in windows?
Can anybody suggest me any resources of the complete installation procedure of postgres-XC in windows. I've installed postgresql-9.2 in my windows and can use it. Now I need to know the installation ...
3
votes
0answers
46 views
What happen when Postgresql tablespace is null?
I'm facing a DWH on postgresql with no DBA (I'm not one) so I turn to you with hope to figure this issue/s.
Long story short, when I check the disk usage per tablespace, I get different distribution ...
-2
votes
1answer
100 views
Use select query in stored procedure
I have some questions about stored procedures.
Using select query inside the stored procedure is efficent or using select query in front end. Which is take less time and give the result is fast or if ...
0
votes
1answer
31 views
PostgreSQL created a PostgreSQL user on mac. Is this necessary?
I just installed PostgreSQL on a Mac (Mountain Lion) today.
The installer created a mac user PostgreSQL on my machine. After installation, the user still exists.
My question is that why postgreSQL ...
4
votes
1answer
128 views
How can I benchmark a PostgreSQL query?
I want to benchmark a query containing an user-defined function I have written for PostgreSQL. Are there any standard ways to run such a benchmark?
I know that timing can be turned on with \timing ...
1
vote
1answer
33 views
How to monitor or do vacuum without stop all?
I have postgres9.1
I try to run full vacuum on a specific table , but it run for more than 5 hours.
I stop all process against this table and i cannot have the process down any longer.
Is there a ...
4
votes
1answer
38 views
How to view the full, flattened query underlying a Postgresql view?
If I have a view on a view on a view (etc.), is there a way to show the full query that this expands/flattens to?
4
votes
2answers
85 views
Seeting up a PostgreSQL database on another hard drive than my system one on OS X?
I am on OS X 10.8.3, I've install PostgreSQL from macports, but I've realized that I have not enough space on my system drive for the DB I am about to populate with data.
Is there a way to create new ...
0
votes
0answers
38 views
Encryption in PostgreSQL
Does PostgreSQL support data encryption features at transactional and the table level of database? If yes, how?
Oracle provides this via database and network encryption. Oracle provides encryption at ...
0
votes
1answer
26 views
Syntax error when trying to import database from two PostgreSQL databases
Following on from this prior question, I'm trying to export a database from one server and put it on another using phpPgAdmin. My process is like this:
select the database in server 1 and hit the ...
0
votes
1answer
135 views
createdb: could not connect to database postgres: FATAL: could not write init file
RedHat Enterprise Server 3.0 32 Bits
psql (PostgreSQL) 8.2.3
user: postgres
server is running:
/soft/postgres/8.2.3/bin/pg_ctl start
pg_ctl: another server may be running; trying to start server ...
4
votes
1answer
80 views
General tool to load dump files
I am a big fan of Postgres both for its price but also for its features.
I am going to have to need to upload into it both Oracle dump and SQL Server files.
I will try to ask and beg for plain .csv ...
1
vote
1answer
42 views
Restrict access for Windows administrator in PostgreSQL
Is there a way to protect PostgreSQL from user with Windows administrator rights?
Like this question but for Postgres.
0
votes
0answers
37 views
Can't login phppgadmin
When I am trying login in phpgpadmin, it shows login failed. Yesterday (and on previous days) phppgadmin was working fine, but now I can't login.
If use the same username and password via PHP code, ...
1
vote
2answers
69 views
Group Database Entries by time difference
I have a large postgreSQL database with log data. All this data has timestamps and I want to group consecutive rows where the difference between the timestamps is less then 1500 miliseconds for ...
0
votes
0answers
64 views
Writing with multiple psql processes to database seems to damage data
I have a couple terabytes of CSV data that I am trying to import into a PostgreSQL 8.4 database (on a RedHat 6.2 server), whose data directory is initialized on a multipath hardware RAID. There are ...
2
votes
1answer
62 views
Is replication from SQL Server 2008 to PostgreSql possible?
Is it possible ? Sql Server as publisher(master) and PostgreSql as slave(subscriber) ? Any type of replication really.
0
votes
0answers
68 views
pg_dump from 9.2 to 8.4
I'm trying to restore a db dump from PostgreSQL 9.2 to PostgrSQL 8.4
(from separate machines, both are running Windows 7).
I followed the instructions in this answer:
Use pg_restore to restore from ...
3
votes
1answer
53 views
Is it safe to call pg_ctl reload while doing heavy writes?
I am running Postgres 8.4, and I would like to change the authentication method for a user. It's my understanding that I have to do this in pg_hba.conf, and then reload the configuration files with ...
0
votes
1answer
100 views
Select first row (grouping) + add aggregate function
First have a look at this question on StackOverflow.
I'm looking to accomplish the same task, except I also need to add an aggregate function (PostGIS's ST_Union) to my query.
How can I combine the ...
0
votes
1answer
308 views
Can't connect to the postgres server ls: /tmp/.s.PGSQL.5432: No such file or directory
*Update*I included an update at the bottom of this post with details from the logs explaining the problem that I haven't been able to fix.
Following along with this Railscast ...
2
votes
2answers
81 views
Changing PostgreSQL port using command line
My PostgreSQL default port is 5432, I want to change default port by using command line (in Windows 7).
I saw this solution before: http://stackoverflow.com/questions/187438/want-to-change-pgsql-port
...
1
vote
4answers
239 views
Daily and Differential Backups
Is there a program with which I can create daily MySQL, PostgreSQL and MariaDB backups with these requirements:
The first time, make a full backup.
The second time, only the changes since the last ...
0
votes
1answer
138 views
Use cases for hstore vs json datatypes in postgresql [closed]
In Postgresql, the hstore and json datatypes seem to have very similar use cases. When would you choose to use one vs. the other? Initial thoughts:
You can nest with json; you can't with hstore
...
1
vote
4answers
161 views
Database design - People and Organisations
Databases are not my primary skill and I am finding I need some assistance please.
The software we are building has "Customers".
The Customer could either be a Person or an Organisation.
I really ...
4
votes
1answer
55 views
force PostgreSQL into recovery mode
I have a somewhat strange question.
Is there a way to manually force PostgreSQL into recovery mode?
I've not been able to find aything in the documentation.
I've been working with a script ...
3
votes
1answer
42 views
Why do psql queries take longer when inserting many rows. Why is it non linear
So if I insert a single row into a postgres database it takes 18 ms. If I do this in a loop like this:
INSERT INTO contacts (numbers)
SELECT distinct array[
(random() * 99999999)::integer,
...
1
vote
1answer
35 views
Foreign Key to Multicolumn Primary Key?
I have three connected table in my design. Basic logic is when a category deleted, also delete records in category_product table which has deleted category's id in its cat_id column. But when I tried ...
0
votes
1answer
48 views
Postgres - Schema information within Trigger?
Background : (Multi-tenant design;separate schema per customer, all table structures identical;Data-warehouse / non-OLTP system here; Postgres 9.2.x).
Reviewing patterns for the following problem: I ...
0
votes
1answer
29 views
OK to put temp tablespace on volatile storage or to omit it from backups? (Postgresql)
I would intuit that it's fine, but I just want to make sure there are
no gotchas from a recovery point of view:
If I were to lose my temp tablespace upon system crash, would this
prevent proper crash ...
1
vote
0answers
30 views
PostgreSQL datasource URL?
My first attempt to use PostgreSQL:
I have the elephant icon on the top right: running on port 5432.
I want to use LibreOffice as an interface:
Connect to an existing database: PostgreSQL
...
0
votes
1answer
62 views
PostgreSQL stored function that returns arbitrary resultset
I would like to write a PostgreSQL stored function that essentially behaves like the stored procedures I know and love from MSSQL and MySQL where I can just wrap a query that takes no parameters and ...
1
vote
2answers
51 views
Read Committed Isolation Level
Quote from docs:
Read Committed is the default isolation level in PostgreSQL. When a transaction uses this isolation level, a SELECT query (without a FOR UPDATE/SHARE clause) sees only data ...
0
votes
0answers
110 views
Performance difference between MySQL and PostgreSQL for the same schema/queries
I'm a newbie DBA, and I have experience in Microsoft SQL Server but I want to jump to FLOSS.
I'm starting a company, and we develop an app (PHP) with a Postgres backend, and we did some tests ...
1
vote
1answer
68 views
Table for optional parent/child relationship
Assuming we have the following table: Item, Parent, Child and Parent is the parent of child.
The item can either belong to a parent or child and not both.
I have other tables that are similar to ...
0
votes
0answers
68 views
PostgreSQL: Slave doesn't replicate the Master's data
I'm using PostgreSQL 9.2.3 on both computers, both of which run Win2008R2. I've setup streaming replication by following this guide: ...
4
votes
2answers
257 views
Is it ever a good idea to denormalize for integrity?
I'm using Postgres 9.2 to develop a quiz app, in which I present the user with a series of problems and record their answers.
These problems can take a variety of forms - they might be multiple ...
0
votes
1answer
36 views
Storing no of players the last seven days in an online game
I want to plot the number of players, in the last seven days, of an MMORPG which haves 50~ servers. The system fetches the number of players in each server every 15 minutes and I'm not really sure how ...
2
votes
1answer
116 views
Postgres: count(*) vs count(id)
I saw in the documentation the difference between count(*) and count(pk). I had been using count(pk) (where pk is a SERIAL PRIMARY KEY) not knowing about the existence of count(*).
My question is ...
1
vote
2answers
51 views
Dropping a group of schemas with similar name patterns
Consider a situation where one need to perform a bunch of essentially identical operations, with the only variable being the name of some object.
In my case, I need to drop some schemas, all of the ...
1
vote
2answers
117 views
Getting SELECT to return a constant value even if zero rows match
Consider this select statement:
SELECT *,
1 AS query_id
FROM players
WHERE username='foobar';
It returns the column query_id with value 1 along with a player's other columns.
How would ...
0
votes
0answers
28 views
Should I keep pg_xlog on the same disk as data if using disk snapshotting?
We're running on EBS volumes on EC2. We're interested in leveraging
EBS snapshotting for backups. However, does this mean we'd need to
ensure our pg_xlog is on the same EBS volume as our data?
(I ...
2
votes
2answers
205 views
Optimizing bulk update performance in Postgresql
Using PG 9.1 on Ubuntu 12.04.
It currently takes up to 24h for us to run a large set of UPDATE
statements on a database, which are of the form:
UPDATE table
SET field1 = constant1, field2 = ...
1
vote
1answer
318 views
Usage of COPY FROM STDIN in postgres
I just started using Postgres and I'm trying to create a sample db to understand its functions, looking around, I found some scripts in pgfoundry.org.
I understand the commands, since I previously ...
0
votes
1answer
36 views
Cannot create PostgreSQL user
I'm using PostrgreSQL 9.1.9 on Ubuntu 13.04.
Using the following StackOverflow question, I tried to create a user/role in postgressql:
...
1
vote
2answers
84 views
Creating a PostgreSQL SERIAL column using pgAdmin3
When I use pgAdmin3 to create a column of type serial, why does it instead create nextval('prices_id_seq'::regclass)?
Should we keep this type defined by pgAdmin? Manually create the table with SQL ...
1
vote
2answers
52 views
“Rewinding” a Postgresql database
I have heard that Postgresql uses an append-only format for it's databases, and if this is true I should be able to 'rewind' the database to a previous point in time by removing the commits that came ...
4
votes
1answer
48 views
Is there better way to 'touch' a row than performing a dummy update?
I use the xmin system column to implement a form of optimistic locking, and sometimes need to 'touch' the row to bump xmin without actually updating the row. I currently just do a 'dummy' update:
...
1
vote
1answer
34 views
Postgresql socket not listening on the right one
I'm trying to get my postgresql to work.
It is clearly running, according to: ps aux | grep postgres | grep -v grep
I am getting -
username 5781 0.0 0.0 2441364 524 ?? Ss ...
