[GENERAL] Inherit Superuser Role Help

[Carlos Mennens]

What am I missing here?

postgres=> SELECT current_user;
 current_user
--
 carlos
(1 row)

postgres=> CREATE DATABASE carlosdb;
ERROR:  permission denied to create database

postgres=> \du
List of roles
 Role name |  Attributes
   | Member of
---+--+---
 carlos|
   | {dba}
 chris |
   | {web}
 dba   | Superuser, Create role, Create DB, Cannot login, Replication | {}
 postgres  | Superuser, Create role, Create DB, Replication   | {}
 web   | Cannot login | {}

Shouldn't 'carlos' be a superuser based on him being a member of a
role which has createdb and superuser rights granted to it?

--
Carlos Mennens


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Role Authentication Failure

[Carlos Mennens]

I dropped both roles (Carlos & DBA) from the database and I will show
you exactly what I'm doing:

postgres=# \du
 List of roles
 Role name |   Attributes   | Member of
---++---
 chris | Create DB, Replication | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

I am creating the new roles as the 'postgres' database user:

postgres=# CREATE ROLE carlos LOGIN CREATEDB CREATEROLE REPLICATION;
CREATE ROLE

postgres=# CREATE ROLE dba NOLOGIN;
CREATE ROLE

postgres=# \du
 List of roles
 Role name |   Attributes   | Member of
---++---
 carlos| Create role, Create DB, Replication| {}
 chris | Create DB, Replication | {}
 dba   | Cannot login   | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

I was curious what happens when I used 'CREATE GROUP' versus 'CREATE
ROLE' so I gave it a shot but looks like it is the same:

postgres=# CREATE GROUP web;
CREATE ROLE

postgres=# \du
 List of roles
 Role name |   Attributes   | Member of
---++---
 carlos| Create role, Create DB, Replication| {}
 chris | Create DB, Replication | {}
 dba   | Cannot login   | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 web   | Cannot login   | {}

postgres=# \password carlos
Enter new password:
Enter it again:

Now I login as 'carlos':

carlos@debian:~$ psql -d postgres
Password:
psql (9.1.9)
Type "help" for help.

postgres=>

but...

--
Carlos Mennens


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Role Authentication Failure

[Carlos Mennens]

I'm doing this all in psql.

Example:

CREATE ROLE carlos LOGIN CREATEDB CREATE ROLE REPLICATION;

Then set password \password carlos

Now I create the ROLE:

CREATE ROLE dba NOLOGIN;

So now I have two roles:

-carlos = user role
-dba = group role

I can login just fine as 'carlos' now with no authentication failure.
But when I do:

GRANT dba TO carlos;

That's the end of 'carlos' being able to login. What am I doing wrong?

The only files I have edited in PostgreSQL post installation is
pg_hba.conf & postgres.conf. It's a new 9.1.9 installation with no
data yet. I just don't understand why granting a role to a user
destroys his authentication and even REVOKE his dba group role doesn't
fix 'carlos'.


On Mon, Apr 15, 2013 at 11:27 AM, Adrian Klaver  wrote:
>
>
> On Mon, Apr 15, 2013 at 7:30 AM, Carlos Mennens 
> wrote:
>>
>> I can't understand what is going on with my PostgreSQL server. For
>> some reason after I GRANT my role 'carlos' to the 'dba' group role, I
>> lose the ability to login. I've reset the password over and over for
>> 'carlos' and even reversing the grant doesn't remedy the
>> authentication failure. The logs are extremely vague:
>>
>> 2013-04-14 00:41:52 EDT FATAL:  password authentication failed for user
>> "carlos"
>>
>>
>
> How are you doing the above, via psql. pgAdmin, other or some combination?
> Do you have a .pgpass file set up?
>
>>
>> As you can see I've removed 'dba' role from 'carlos' but he still
>> can't login. My only option is to DROP the 'carlos' role and re-create
>> him.
>>
>> The problem occurs when I:
>>
>> GRANT dba TO carlos;
>>
>> I don't understand which role (carlos or dba) needs INHERIT or
>> NOINHERIT if that's causing this...
>>
>>
>> --
>> Carlos Mennens
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>
> --
> Adrian Klaver
> adrian.kla...@gmail.com



-- 
Carlos Mennens


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Role Authentication Failure

[Carlos Mennens]

I can't understand what is going on with my PostgreSQL server. For
some reason after I GRANT my role 'carlos' to the 'dba' group role, I
lose the ability to login. I've reset the password over and over for
'carlos' and even reversing the grant doesn't remedy the
authentication failure. The logs are extremely vague:

2013-04-14 00:41:52 EDT FATAL:  password authentication failed for user "carlos"

My roles look as follows:

postgres=# \dg
 List of roles
 Role name |   Attributes   | Member of
---++---
 carlos| Superuser, Create role, Create DB, Replication | {}
 dba   | No inheritance, Cannot login   | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

As you can see I've removed 'dba' role from 'carlos' but he still
can't login. My only option is to DROP the 'carlos' role and re-create
him.

The problem occurs when I:

GRANT dba TO carlos;

I don't understand which role (carlos or dba) needs INHERIT or
NOINHERIT if that's causing this...


--
Carlos Mennens


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL Backup Booklet

[Carlos Mennens]

Based on Amazon reviews and others in the PG community, I would 100% get
something like this. High Availability / Fail Over are my biggest concerns
with PostgreSQL Database.

Thanks so much for sharing and good luck!!!


On Wed, Apr 3, 2013 at 10:09 AM, Shaun Thomas wrote:

> Hey!
>
> So, Packt approached me a few months ago and asked me to put together a
> very basic series of short step-by-step instructions on backing up
> PostgreSQL. The title is "Instant PostgreSQL Backup and Restore How-to."
>
> I tried to cover all of the basic approaches used by most admins, and I
> tested it on a guy at work who's a SQL Server DBA but recently took over
> care and feeding of a PG database. He said it was easier to understand than
> the documentation, at least for just getting everything working and sorting
> out more advanced details later.
>
> I know it's not exactly Greg Smith's performance book, but I'm glad to
> contribute how I can. I'm not entirely sure it's worth adding to the book
> page:
>
> http://www.postgresql.org/**docs/books/<http://www.postgresql.org/docs/books/>
>
> But if it is, I'll provide any help or extra information necessary. If
> anyone has questions, I'm here to answer them. :)
>
> --
> Shaun Thomas
> OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
> 312-676-8870
> stho...@optionshouse.com
>
> __**
>
> See 
> http://www.peak6.com/email_**disclaimer/<http://www.peak6.com/email_disclaimer/>for
>  terms and conditions related to this email
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general>
>



-- 
Carlos Mennens

[GENERAL] Can't Drop Role

[Carlos Mennens]

For some reason I'm removed all grants and roles from this users
account but I'm unable to remove / drop him from the database as
follows:

postgres=# \du
 List of roles
 Role name |   Attributes
 | Member of
---++---
 cmennens  | No inheritance, Cannot login   | {}
 jmadeline | Superuser, No inheritance, Create role, Create DB, Replication | {}
 mrbs  | No inheritance | {}
 mwilshaw  | No inheritance | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 rcube | No inheritance | {}

postgres=# DROP ROLE cmennens;
ERROR:  role "cmennens" cannot be dropped because some objects depend on it
DETAIL:  owner of function pg_logfile_rotate()
owner of function pg_file_length(text)
owner of function pg_file_read(text,bigint,bigint)
owner of function pg_logdir_ls()
owner of function pg_file_unlink(text)
owner of function pg_file_rename(text,text)
owner of function pg_file_rename(text,text,text)
owner of function pg_file_write(text,text,boolean)
owner of extension adminpack


-- 
Carlos Mennens


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Sample databases

[Carlos Mennens]

On Wed, Jan 16, 2013 at 9:53 AM, Vraj Mohan  wrote:
> Is there a good sample database (with decent data volumes) for
> postgresql? I am interested in one for learning and automated testing.
>
> I looked at http://pgfoundry.org/projects/dbsamples/ (specifically at
> pagila), but it seemed incomplete and not maintained,

I've spent some months searching a year ago and found the same thing
as you. All the sample databases for PG are terrible. They're old, not
maintained, incomplete, or just useless. I too would love to have a
sample database with large data and relational values. Sadly unless
you create one yourself, I don't think there's one publicly available
that meets our expectations.

-- 
Carlos Mennens


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Dedicated PostgreSQL System

[Carlos Mennens]

Hello All,

I'm trying to understand what exact parameters or configurations are
adjusted when a PostgreSQL database system is going to be used as a
'stand-alone' or 'dedicated' server versus a shared or embedded
database system? I have a server that's only going to be dedicated to
running Linux and PostgreSQL software. Can someone please help me
understand a few things I need to view or test with in order to get
the most utilization from PostgreSQL & the dedicated hardware it will
sit on top?

--
Carlos Mennens


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Automated Backup Script Help (Linux)

[Carlos Mennens]

On Wed, Mar 7, 2012 at 2:08 PM, John R Pierce  wrote:
> whats that pg_backup.sh script look like?  you're getting shell errors on
> line 7 and 8 of the script.

I didn't attach it since it's the same as the link I referenced in my
original post. I'll attach it in here for those that don't or can't
download it:

> this has nothing to do with postgresql.

It's a scrip to backup and interact with the database so I think it
does. It may not be specifically what this mailing list was intended
for but it's the best suited that I could find not to mention the
script is posted on the main PostgreSQL wiki site so it appears to be
legit and supported...

http://wiki.postgresql.org/wiki/Automated_Backup_on_Linux

Thanks for any info!
#!/bin/bash
 
###
### LOAD CONFIG ###
###
 
SCRIPTPATH=$(cd ${0%/*} && pwd -P)
source $SCRIPTPATH/pg_backup.config
 
 
###
 PRE-BACKUP CHECKS 
###
 
# Make sure we're running as the required backup user
if [ $BACKUP_USER != "" -a "$(id -un)" != "$BACKUP_USER" ]; then
echo "This script must be run as $BACKUP_USER. Exiting."
exit 1;
fi;
 
 
###
### INITIALISE DEFAULTS ###
###
 
if [ ! $HOSTNAME ]; then
HOSTNAME="localhost"
fi;
 
if [ ! $USERNAME ]; then
USERNAME="postgres"
fi;
 
 
###
 START THE BACKUPS 
###
 
 
FINAL_BACKUP_DIR=$BACKUP_DIR"`date +\%Y-\%m-\%d`/"
 
echo "Making backup directory in $FINAL_BACKUP_DIR"
 
if ! mkdir -p $FINAL_BACKUP_DIR; then
echo "Cannot create backup directory in $FINAL_BACKUP_DIR. Go and fix 
it!"
exit 1;
fi;
 
 
###
### SCHEMA-ONLY BACKUPS ###
###
 
for SCHEMA_ONLY_DB in ${SCHEMA_ONLY_LIST//,/ }
do
SCHEMA_ONLY_CLAUSE="$SCHEMA_ONLY_CLAUSE or datname ~ '$SCHEMA_ONLY_DB'"
done
 
SCHEMA_ONLY_QUERY="select datname from pg_database where false 
$SCHEMA_ONLY_CLAUSE order by datname;"
 
echo -e "\n\nPerforming schema-only backups"
echo -e "\n"
 
SCHEMA_ONLY_DB_LIST=`psql -h "$HOSTNAME" -U "$USERNAME" -At -c 
"$SCHEMA_ONLY_QUERY" postgres`
 
echo -e "The following databases were matched for schema-only 
backup:\n${SCHEMA_ONLY_DB_LIST}\n"
 
for DATABASE in $SCHEMA_ONLY_DB_LIST
do
echo "Schema-only backup of $DATABASE"
 
if ! pg_dump -Fp -s -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" | gzip > 
$FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz.in_progress; then
echo "[!!ERROR!!] Failed to backup database schema of $DATABASE"
else
mv $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz.in_progress 
$FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz
fi
done
 
 
###
## FULL BACKUPS ###
###
 
for SCHEMA_ONLY_DB in ${SCHEMA_ONLY_LIST//,/ }
do
EXCLUDE_SCHEMA_ONLY_CLAUSE="$EXCLUDE_SCHEMA_ONLY_CLAUSE and datname !~ 
'$SCHEMA_ONLY_DB'"
done
 
FULL_BACKUP_QUERY="select datname from pg_database where not datistemplate and 
datallowconn $EXCLUDE_SCHEMA_ONLY_CLAUSE order by datname;"
 
echo -e "\n\nPerforming full backups"
echo -e "\n"
 
for DATABASE in `psql -h "$HOSTNAME" -U "$USERNAME" -At -c "$FULL_BACKUP_QUERY" 
postgres`
do
if [ $ENABLE_PLAIN_BACKUPS = "yes" ]
then
echo "Plain backup of $DATABASE"
 
if ! pg_dump -Fp -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" | 
gzip > $FINAL_BACKUP_DIR"$DATABASE".sql.gz.in_progress; then
echo "[!!ERROR!!] Failed to produce plain backup 
database $DATABASE"
else
mv $FINAL_BACKUP_DIR"$DATABASE".sql.gz.in_progress 
$FINAL_BACKUP_DIR"$DATABASE".sql.gz
fi
fi
 
if [ $ENABLE_CUSTOM_BACKUPS = "yes" ]
then
echo "Custom backup of $DATABASE"
 
if ! pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" -f 
$FINAL_BACKUP_DIR"$DATABASE".custom.in_progress; then
echo "[!!ERROR!!] Failed to produce custom backup 
database $DATABASE"
else
mv $FINAL_BACKUP_DIR"$DATABASE".custom.in_progress 
$FINAL_BACKUP_DIR"$DATABASE".custom
fi
fi
 
done
 
echo -e "\nAll database backups complete!"

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Automated Backup Script Help (Linux)

[Carlos Mennens]

I'm trying to get the automated backup scripts to work from:

http://wiki.postgresql.org/wiki/Automated_Backup_on_Linux

Currently I'm using PostgreSQL 9.1.3 and have the following three files:

carlos@db1:~/postgresql$ ls -l
total 20
drwxr-xr-x 2 carlos users 4096 Mar  7 13:13 backup
-rw-r--r-- 1 carlos users 1434 Mar  7 13:19 pg_backup.config
-rw-r--r-- 1 carlos users 4304 Mar  7 12:54 pg_backup_rotated.sh
-rw-r--r-- 1 carlos users 3379 Mar  7 12:54 pg_backup.sh

My Linux shell user 'carlos' has rwx to the entire directory as well
as the database role 'carlos' is listed as a REPLICATION role:

postgres=# \du
 List of roles
 Role name |   Attributes
 | Member of
---++---
 carlos| Superuser, No inheritance, Create role, Create DB,
Replication | {it}

Now when I attempt to run the script for the 1st time, I get the
following error:

carlos@db1:~/postgresql$ pwd
/home/carlos/postgresql

carlos@db1:~/postgresql$ ls -l
total 20
drwxr-xr-x 2 carlos users 4096 Mar  7 13:13 backup
-rw-r--r-- 1 carlos users 1435 Mar  7 13:25 pg_backup.config
-rw-r--r-- 1 carlos users 4304 Mar  7 12:54 pg_backup_rotated.sh
-rw-r--r-- 1 carlos users 3379 Mar  7 12:54 pg_backup.sh

carlos@db1:~/postgresql$ sh pg_backup.sh
pg_backup.sh: 7: cd: can't cd to pg_backup.sh
pg_backup.sh: 8: pg_backup.sh: source: not found
pg_backup.sh: 16: [: !=: unexpected operator
Making backup directory in 2012-03-07/
pg_backup.sh: 54: pg_backup.sh: Bad substitution

So the only thing this script is doing for me is creating an empty
folder with the date as the name. Any idea what I'm doing wrong? I've
attached the configuration file since this is the only thing I was
told that needs to be modified. The contents are exactly as they are
on my server.


pg_backup.config
Description: Binary data

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Defining Role Privileges

[Carlos Mennens]

I'm wondering about my CREATE ROLE statements for PostgreSQL. I guess
I don't know if there's an official answer but I feel like I'm
entering a lot of redundant privileges to a role for example:

CREATE ROLE tom NOINHERIT LOGIN SUPERUSER CREATEDB CREATEROLE REPLICATION;
CREATE ROLE

My question is do I need to specify CREATEDB & CREATEROLE if I'm
already granting the SUPERUSER privilege? Seems kind of redundant to
me, no? Is there any logical reason someone would be a SUPERUSER and
not have CREATEDB or CREATEROLE?

Also when I generate a new role, is there any difference between using:

ALTER ROLE tom ENCRYPTED PASSWORD 'md5081bea17b5503506d29531af33cc6f4e';

\password tom

Is there a downside to using the \password psql command? Is it also
encrypted like the statement above? How do you create roles and do you
do it manually or have some kind of template?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Puzzling full database lock

[Carlos Mennens]

On Wed, Feb 1, 2012 at 7:51 PM, Christopher Opena  wrote:
> It's CentOS 5.5, PostgreSQL version 9.0.4 (x86_64).

That seems extremely bleeding edge for CentOS. Did you compile this
package from source RPM or some 3rd party package maintainer for
PostgreSQL?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Puzzling full database lock

[Carlos Mennens]

On Wed, Feb 1, 2012 at 7:38 PM, Christopher Opena  wrote:
> Hello folks,
>
> We've been running into some very strange issues of late with our PostgreSQL
> database(s).  We have an issue where a couple of queries push high CPU on a
> few of our processors and the entire database locks (reads, writes, console
> cannot be achieved unless the high CPU query procs are killed).  Further
> investigation shows ~59% total cpu usage (we have 16 total cores), low io,
> and mid-to-low memory usage (we have 74GB of memory, shared_buffers=16GB).

Just out of curiosity, what OS are you running?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Data Type for Money

[Carlos Mennens]

On Fri, Dec 30, 2011 at 12:46 PM, Adrian Klaver  wrote:
> My guess is it is listed as numeric which is equivalent to decimal:
>
> http://www.postgresql.org/docs/9.1/interactive/datatype-numeric.html

Thanks. I just for some reason can't see or understand the difference
between 'decimal' & 'numeric'. Why have two data types for the same
values? Am I missing something?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Dated Version of PostgreSQL

[Carlos Mennens]

2011/12/30 Devrim GÜNDÜZ :
> Hi,
>
> On Fri, 2011-12-30 at 11:50 -0500, Carlos Mennens wrote:
>> My question is how exactly would I
>> install the latest version of PostgreSQL (9.1.2) on RHEL 6.2?
>
> We have PostgreSQL yum repository:
>
> http://yum.postgresql.org
>
> Install repository RPM first:
>
> http://yum.postgresql.org/9.1/redhat/rhel-6-x86_64/pgdg-redhat91-9.1-5.noarch.rpm
>
> and now you can install PostgreSQL 9.1 with:
>
> yum install postgresql91-server
>
> http://yum.postgresql.org/9.1/redhat/rhel-6-x86_64/repoview/
>
> has the full list of packages for RHEL 6 - PostgreSQL 9.1. You can also
> find lots of 3rd party software.

Oh that's amazing! Question, how do you keep this package updated?
Does it auto update when a new version is released and I run the 'yum
upgrade postgresql' command or do I need to run a special command to
update off this special repository?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Data Type for Money

[Carlos Mennens]

I'm trying to understand what is the recommended data type for $ in
PostgreSQL. I've done some research and from what I've gathered, there
are a few options:

1. decimal
2. money

I've read the 'money' data type is non-standard and I should avoid
using this. I see it a bunch of Microsoft SQL Server which I assume
works great but I'm using PostgreSQL and want to make sure I'm ANSI
SQL compliant. I would normally just use 'decimal' however when I'm in
doubt, I use pgAdmin3 as a cheat sheet and upon building a new column,
under 'data type', there is no option for decimal but there is for
money. I'm very confused as I assumed 'money' was a non-standard
option for SQL Server and 'decimal' was the correct value but it's not
an option in the pgAdmin3 GUI.

Any tips and or advice?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Dated Version of PostgreSQL

[Carlos Mennens]

Yeah I absolutely despise Gentoo for that exact reason. I don't have
that kind of time and patience to spend on keeping my system up and
running. I've spoken with the team and we've agreed to install Debian
Linux which is my distribution of choice and it supports 9.1.2.

Thanks!

On Fri, Dec 30, 2011 at 12:08 PM, Bèrto ëd Sèra  wrote:
> Hi,
>
> no, usually it's a three step thing: configure, make, make install. However,
> I suggest you have a look at the README file in the source code. I always
> compile my own stuff as I run gentoo (so no binaries at all around) but each
> distro has its own peculiar things. Bear in mind that binaries usually have
> no header files for needed dependancies, so you'll probably need to install
> a number of -dev packages to get what you need.
>
> Being online on irc in the channel that deals with your distro might help.
> However, the worst that can happen is that it stops before compiling, so no
> big deal.
>
> Bèrto
>
>
> On 30 December 2011 18:59, Carlos Mennens  wrote:
>>
>> I've never compiled anything from source. Is this difficult if I've
>> never done so on *NIX systems?
>>
>> On Fri, Dec 30, 2011 at 11:54 AM, Bèrto ëd Sèra 
>> wrote:
>> > Hi!
>> >
>> >>
>> >> My question is how exactly would I
>> >> install the latest version of PostgreSQL (9.1.2) on RHEL 6.2? Is there
>> >> an unsupported RPM for 9.1.2?
>> >
>> >
>> > Why don't you just compile it from source? If it has to be unsupported
>> > anyway, then this is probably much quicker.
>> >
>> > Bèrto
>> >
>> > --
>> > ==
>> > If Pac-Man had affected us as kids, we'd all be running around in a
>> > darkened
>> > room munching pills and listening to repetitive music.
>
>
>
>
> --
> ==
> If Pac-Man had affected us as kids, we'd all be running around in a darkened
> room munching pills and listening to repetitive music.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Dated Version of PostgreSQL

[Carlos Mennens]

I've been asked to stand up a dedicated database server for a new
office. They're only requirement is the server run RHEL 6.2 64-bit. I
told them no problem as I'm very familiar with Linux and installing /
configuring PostgreSQL. So after I've installed RHEL 6.2, I then
installed PostgreSQL and the only version available in the Red Hat Yum
repositories was 8.4.9. I called them and they told me that this is
why Red Hat is extremely popular due to stability. They don't support
bleeding edge release packages. My question is how exactly would I
install the latest version of PostgreSQL (9.1.2) on RHEL 6.2? Is there
an unsupported RPM for 9.1.2? How do you guys who manage Red Hat
servers install recent versions of PostgreSQL?

Thanks for any help!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How To Handle Hung Connections

[Carlos Mennens]

I'm attempting to delete a database that I've obviously not closed
connections from cleanly.

postgres=# DROP DATABASE filters;
ERROR:  database "filters" is being accessed by other users
DETAIL:  There are 4 other session(s) using the database.

How exactly would one manage this issue from a PostgreSQL
administration stand point? I know there are not real users connected
to this database but rather zombie connections. Is there a way to
force the DROP command or can I manually view / kill connections to
this database w/o having to restart the entire PostgreSQL daemon and
impact other active databases?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Streaming Replication Configuration

[Carlos Mennens]

I'm attempting today to get streaming replication from the Wiki
configured on my two 9.1.2 servers:
http://wiki.postgresql.org/wiki/Streaming_Replication

I'm on step #6

6. Make a base backup by copying the primary server's data directory
to the standby server.

$ psql -c "SELECT pg_start_backup('label', true)"
$ rsync -a ${PGDATA}/ standby:/srv/pgsql/standby/ --exclude postmaster.pid
$ psql -c "SELECT pg_stop_backup()"

When I attempt the 1st command listed on my primary (master) server, I
get this error:

carlos@db1:~$ psql -c "SELECT pg_start_backup('label', true)"
psql: FATAL:  database "carlos" does not exist

Now I'm showing they want me to use the -c switch to connect but no
database is defined so is it implied that I need to add 'postgres'
maintenance database in there or something else?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Server/Data Migration Advice

[Carlos Mennens]

On Thu, Dec 15, 2011 at 11:09 AM, Raymond O'Donnell  wrote:
> The point here is that with the plain-text dump (the default output from
> pg_dump), you can feed that directly to psql; but you have no control
> over what is restored, or in what order, without editing the dump file
> directly.
>
> If, however, you using one of the other output options, you need to use
> pg_restore; but you can do all sorts of things with the restore.

I think I now understand and sorry. I have not been using PostgreSQL
long at all and it's my first ever venture into any RDBMS and even
ANSI SQL.

So basically:

pg_dump = plain text dumps which can be read by 'psql' & 'pg_restore'.

pg_dump -Fc = custom / compressed dumps that must be used by
pg_restore only with options to pick / choose what I want from the
dump.

I hope I got that right. I think I've learned a lot from you guys
pointing me in the right direction and RTFM.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Server/Data Migration Advice

[Carlos Mennens]

On Thu, Dec 15, 2011 at 10:28 AM, Adrian Klaver  wrote:
> The reason I pointed to the manual links is that there is a lot of good
> information in there. It deserves more than a skim:). Realistically, to get 
> the
> most out of the dump/restore process you need to know the options available on
> both sides of the procedure.  For instance the -C option to pg_dump, puts in
> command to create database on restore, saves the step of creating a database 
> on
> the other end.  The issues that may arise are most likely going to be generic 
> to
> the upgrade from 8.4 to 9.1. To get a handle on those it is best to read the
> Release Notes for the  covered versions, in particular the Migration section. 
> In
> this case the notes for 9.0 that cover the migration issues from 8.4 and the
> notes for 9.1 that cover same from 9.0. Not all the issues may affect you, it
> depends on what you have done in your database. What form of pg_dump you use 
> is
> up to you.  I will say, the custom format, -Fc, has some interesting features.
> One, it is compressed. Two, you can restore from it in full or pick and
> choose(within reason,see docs) those items you wish to restore without 
> resorting
> to cut and paste.

On Thu, Dec 15, 2011 at 10:28 AM, Adrian Klaver  wrote:
> The reason I pointed to the manual links is that there is a lot of good
> information in there. It deserves more than a skim:). Realistically, to get 
> the
> most out of the dump/restore process you need to know the options available on
> both sides of the procedure.  For instance the -C option to pg_dump, puts in
> command to create database on restore, saves the step of creating a database 
> on
> the other end.  The issues that may arise are most likely going to be generic 
> to
> the upgrade from 8.4 to 9.1. To get a handle on those it is best to read the
> Release Notes for the  covered versions, in particular the Migration section. 
> In
> this case the notes for 9.0 that cover the migration issues from 8.4 and the
> notes for 9.1 that cover same from 9.0. Not all the issues may affect you, it
> depends on what you have done in your database. What form of pg_dump you use 
> is
> up to you.  I will say, the custom format, -Fc, has some interesting features.
> One, it is compressed. Two, you can restore from it in full or pick and
> choose(within reason,see docs) those items you wish to restore without 
> resorting
> to cut and paste.

So after reading
http://www.postgresql.org/docs/9.1/interactive/backup-dump.html,

I'm not sure why the manual shows you in "24.1. SQL Dump" & then
directly after in 24.1.1, they explain how to restore with psql as you
advised me not to. I got my psql db_name < infile command directly
from the manual. I know it's personal preference but from everything
you noted, why didn't they just explain how to perform a pg_restore in
the "24.1.1. Restoring the Dump" section.

"24.1.3. Handling Large Databases" section is very cool but also
extremely vague IMO.

> Use pg_dump's custom dump format. If PostgreSQL was built on a system with 
> the zlib
> compression library installed, the custom dump format will compress data as 
> it writes it to
> the output file. This will produce dump file sizes similar to using gzip, but 
> it has the added
> advantage that tables can be restored selectively. The following command 
> dumps a
> database using the custom dump format:

So this seems helpful to myself in only that A: the dump is compressed
(my databases are generally small anyways) and B: I don't have to
create the database before I restore it. My only question is I see
that noted nowhere in the manual ... yet but I'm just wondering if
that's a correct statement.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Server/Data Migration Advice

[Carlos Mennens]

On Thu, Dec 15, 2011 at 9:37 AM, Adrian Klaver  wrote:
> You know the fine manual covers this?:
> http://www.postgresql.org/docs/9.1/interactive/app-pgdump.html

I've honestly skimmed the manual and it's very easy to use and good
but it covers so many aspects and sometimes I need a specific answer
for my situation.

> In the case above you seemed to have used the plain text format, in the future
> should you use a non text format the restore process is here:
> http://www.postgresql.org/docs/9.1/interactive/app-pgrestore.html

Are you saying I dumped the database in a text formal or I'm restoring
the database infile in plain text? I'll read that 2nd URL link you
posted. What are the issues with using pg_dump to dump and then 'psql
some_db < pg_dump.sql'? I show the manual explains how to perform this
action and what the system is doing but doesn't explain why this is an
issue or why you recommended to use pg_restore instead?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Server/Data Migration Advice

[Carlos Mennens]

On Mon, Dec 12, 2011 at 12:23 PM, Andreas Kretschmer
 wrote:
> - use the pg_dumpall from the new version to make the dump, for instance
>  pg_dumpall -h  ... | psql (something like this, on the new
>  host)

I performed a pg_dump from my new 9.1.2 server but my question now is
importing the data. So I had 9.1.2 perform the dump and it's now on
the new server but what are (if any) the steps to restore or install
them into my new 9.1.2 server? Is there anything I need to do to the
dump .sql file before using psql to restore the dump file?

pg_dump -h old_db > old_db.sql

That command above was the command I issued from new_db (new server)
to dump all the 8.4.8 data onto my 9.1.2 machine. What is the next
course of action? Is there a conversion process or something I need to
do or simply restore it? Can I simply do:

psql webmail < old_db.sql

Is that sufficient?

> Du you have BYTEA-Columns? The default output-format changed. Some other
> details changed too, read the release notes!

Is there a way in psql client I can search my database tables for any
BYTEA columns?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Locking Tables & Backup Inquiry

[Carlos Mennens]

On Wed, Dec 14, 2011 at 1:38 PM, Andy Colson  wrote:
> this'll run every hour.
>
>> 0 * * * * /usr/bin/pg_dumpall>  pg_dumpall.$DATE.sql

Thank you!

> try:
>
> 0 4 * * * /usr/bin/pg_dumpall>  pg_dumpall.$DATE.sql
>
> that'll run at 4am every day.

When I run the command in my shell (not in Cron), I'm prompted for my
login password. Should I change the permissions in pg_hba.conf and
enable INHERIT grants on my user? Should I place this in who's Cron
line? Postgres? Carlos? or Root?
>
> Watch the path's, who know's what directory is current:
>
> 0 4 * * * /usr/bin/pg_dumpall > /backup/pg_dumpall.$DATE.sql

Yes, I always check my paths and use full paths rather than symbolic links.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Locking Tables & Backup Inquiry

[Carlos Mennens]

On Wed, Dec 14, 2011 at 1:15 PM, Andy Colson  wrote:
> Yep, you simply cron a pg_dump.  (dumpall if you want users/roles and all
> databases).  No locking needed.

So how would one put this in cron if I wanted to run this everyday?

0 * * * * /usr/bin/pg_dumpall > pg_dumpall.$DATE.sql

Will that work above assuming I wanted to run this every day at that
specific time? I'm just guessing since I've never created a Crontab or
messed with PG backups.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Locking Tables & Backup Inquiry

[Carlos Mennens]

I'm wanted to find out why is it recommended or even an option to lock
tables during a backup of a database? I've never experimented with
database backups so I'm only  guessing it locks / freezes the data so
no changes can be made while the backup is in process, correct? Just
curious and wasn't able to find an answer online.

My next question is more complex but more of a recommendations. I'm
looking to see how do you guys backup your databases? Do you simply
cron 'pg_dump' command line or do you have a script that gets called
in cron using 'pg_dump' / 'pg_dumpall'? Just looking for ideas /
recommendations for a simple / quick way to back up 5 small databases
on my server.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Server/Data Migration Advice

[Carlos Mennens]

On Mon, Dec 12, 2011 at 12:23 PM, Andreas Kretschmer
 wrote:
> - you should use 9.1.2, not 9.1.1 ;-)

I don't think it's available yet in Debian repositories. I can only
use whatever packages they've compiled in their repositories.

> - use the pg_dumpall from the new version to make the dump, for instance
>  pg_dumpall -h  ... | psql (something like this, on the new
>  host)

So it would look like this from my new server?

pg_dumpall -h old_db > mydata.sql

> Du you have BYTEA-Columns? The default output-format changed. Some other
> details changed too, read the release notes! I don't think I have any BYTEA 
> columns so I think I'm OK. I'll read the release notes...

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Server/Data Migration Advice

[Carlos Mennens]

I've finally received my new virtual database server (Debian Linux) &
this weekend I'll be rolling my new PostgreSQL server online. My
question is I've never migrated production data from 8.4.8 to 9.1.1. I
would like to find out from the community what exactly is the
recommended process in moving my database server from 8.4.8 to 9.1.1.
I'm not simply upgrading the existing server as I will be installing
PostgreSQL 9.1 on the new hardware and not sure if it's as simple as
simply performing a pg_dumpall:

pg_dumpall > mydata.sql

I don't know if I need to use some kind of conversion tool to convert
the data from 8.4.8 so that it's compatible with 9.1.1 so if you guys
could please shine in on any recommendations, I would greatly
appreciate it!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Create Tables As Specific Role

[Carlos Mennens]

I'm installing a calendar application called MRBS. The installation
instructions require I create a role and database specifically for
this web application. I'm currenlt logged in as my user account
'carlos' which is a superuser.

postgres=# SELECT current_user;
 current_user
--
 carlos
(1 row)

I've already created the role 'mrbs' for which will own the database
and all it's tables:

Role name |   Attributes   | Member of
---++---
 carlos| Superuser, Create role, Create DB, Replication | {}
 mrbs  || {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

Now I'm creating the database & I've set the owner to the 'mrbs' role:

 Name|  Owner   | Encoding |   Collate   |Ctype|   Access
privileges
---+--+--+-+-+---
 calendar  | mrbs | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

Now I need to have PostgreSQL run a file in my /tmp directory which
will create the tables. The instructions from the MRBS documentation
say:

"Create the MRBS tables using the supplied tables.*.sql file:

[PostgreSQL]$ psql -a -f tables.pg.sql mrbs"

If I do the suggested above, my user 'carlos' will own all the tables
in the database 'calendar' which is owned my 'mrbs'. How can I execute
the command above but force PostgreSQL to create the files as a
different user and not 'carlos'?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication Across Two Servers?

[Carlos Mennens]

On Fri, Nov 4, 2011 at 11:56 AM, Prashant Bharucha <
prashantbharu...@yahoo.ca> wrote:

> Hi Carlos
>
> Use Slony "master to multiple slaves" replication system for PostgreSQL
>  supporting cascading (*e.g.* - a node can
> feed another node which feeds another node...) and failover.
> http://slony.info/
>
>
I'm not sure I see the point of using a third party application to do
something PostgreSQL can do natively. Am I missing something here?

Re: [GENERAL] Replication Across Two Servers?

[Carlos Mennens]

On Fri, Nov 4, 2011 at 11:52 AM, Brandon Phelps  wrote:
> Carlos,
>
> Streaming replication was introduced in PostgreSQL 9.0 and should do what
> you want.
>
> http://wiki.postgresql.org/wiki/Streaming_Replication

Oh great! I didn't see that in the 8.4 manual since that is what
Debian 6 has as the most stable version in it's package manager.
Anyone know of a stable Linux distribution that offers 9.0+? I know
Debian Wheezy (testing) has 9.1 but sadly it's testing and not
recommended for production utilization. RHEL is years behind as far as
packages go which makes them stable to an annoying degree.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Replication Across Two Servers?

[Carlos Mennens]

We had a 8.4.8 production server of PostgreSQL on a Dell blade server
which ran for 3 years fine. The server housed all our database needs
perfectly but sadly the entire machine died. The drives were dead and
the motherboard was fried but we did have daily full backups of the
entire machine. Today I received our new blade servers which will run
VMware & I get to create two new PostgreSQL servers. I wanted to make
a master database server and a slave in case the master dies. My
question is does PostgreSQL 8.4 or 9.1support synchronization between
two physical machines over Ethernet? I've never replicated any kind of
database before so I don't know if that's possible and the more I
search this on my own, the more confused I am. It appears in
PostgreSQL, the word "replication" has several different meanings.

If you had to stand up two individual Debian Linux servers running a
specific version of PostgreSQL, could / would you be able to have the
master also synchronize all data to a slave server?

Thanks for any info!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres Account Inherit Question

[Carlos Mennens]

On Fri, Oct 14, 2011 at 12:44 PM, Julien Rouhaud  wrote:
>
> Hi
> Did you check for a .pgpass file ?

I'm assuming you're talking about a hidden file in my Linux shell for
the 'postgres' user. I don't see one anywhere. I just had a
.psql_history file which I removed.

On Fri, Oct 14, 2011 at 1:31 PM, Guillaume Lelarge
 wrote:
>> Did you check for a .pgpass file ?
>
> And do you have any other lines before the few ones you give ?

Yes. It reads the config file as follows:


# Database administrative login by UNIX sockets
local   all postgres  ident

# TYPE  DATABASEUSERCIDR-ADDRESS  METHOD

# "local" is for Unix domain socket connections only
local   all all   md5
# IPv4 local connections:
hostall all 127.0.0.1/32  md5
hostall all 10.1.10.0/24  md5
hostall all 10.1.11.0/24  md5
hostall all 192.168.0.0/24md5
# IPv6 local connections:
hostall all ::1/128   md5

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Confused About pg_* Tables

[Carlos Mennens]

I'm confused about how I'm able to access the following pg_* tables
regardless of connected database. I thought these tables were hidden
or stored in the 'postgres' database but I'm still able to access this
data regardless of which database I'm connected to:

Code:

zoo=# SELECT * FROM pg_user;
 usename  | usesysid | usecreatedb | usesuper | usecatupd | userepl |
passwd  | valuntil | useconfig
--+--+-+--+---+-+--+--+---
 postgres |   10 | t   | t| t | t   |
 |  |
 carlos   |16384 | t   | t| t | t   |
 |  |
(2 rows)

When I use my tab key in 'psql' after the 'FROM' statement, I'm
presented with a ton of what I presume to be tables however when I
check for tables in the 'postgres' database, I get nothing. I'm
confused...

Code:

psql (9.1.1, server 9.1.1)
You are now connected to database "postgres".
postgres=# \d
No relations found.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Postgres Account Inherit Question

[Carlos Mennens]

I've configured my 'pg_hba.conf' file to look as follows:

# "local" is for Unix domain socket connections only
local   all all   md5
# IPv4 local connections:
hostall all 127.0.0.1/32  md5
hostall all 192.168.0.0/24md5

Now I've reloaded / restarted the PostgreSQL daemon however for some
reason when I use the 'postgres' user locally, it never prompts for a
password in 'psql'. I've altered the role to NOINHERIT

postgres=# ALTER ROLE postgres NOINHERIT;
ALTER ROLE

Any other role locally requires a password to even list the database
using 'psql -l' command except the 'postgres' role. Is this normal
behavior or am I missing something here? How can I force the postgres
account to be prompted for a password when communicating to the
database server locally?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Backup Database Question

[Carlos Mennens]

On Thu, Oct 6, 2011 at 3:12 PM, John R Pierce  wrote:
>   /path/to/pg_dumpall | gzip > /path/to/pgbackup-$(date -I).sql.gz

Thanks John. I've never written a script so do I just use 'Vim' to
open a new file and just paste the following line?

#!/bin/bash
/usr/bin/pg_dumpall | gzip > /var/db_backup/pg_backup-$(date -I).sql.gz

Is that all I need to do or is there more steps / data involved?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Tuning Variables For PostgreSQL

[Carlos Mennens]

I read all the time that most DBA's are required or should tune their
DBMS which obviously in my case would be PostgreSQL but I'm curious
what exactly is involved when tuning a DBMS like PostgreSQL. What are
some of the basic functions involved when tuning? Are there specific
things I should tweak on a newly built server freshly installed with
PostgreSQL? Can someone please clarify what exactly most people do
when they "tune"? Lastly I'm sure this has been discussed but after a
Google search, I can't find any updated info since 2009 so I would
like to know specifically what file system you've found PostgreSQL to
work on the best? What file system will give me the best performance
and stability on disk? I've currently running it on Linux Ext4 file
system and have no had any issues but I was wondering if there was
anything out there more suited to perform better on PostgreSQL.

Thanks!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Backup Database Question

[Carlos Mennens]

On Thu, Oct 6, 2011 at 11:19 AM, Tom Lane  wrote:
> Use pg_dumpall.  The extra time to dump the user and database
> definitions is unlikely to be noticeable, and if push comes to shove
> you'll be glad you had them.

Yes I agree but I didn't know enough about PostgreSQL to make that
determination. Seems very logical however. Does anyone know of a
PostgreSQL backup script floating around the Internet for Linux
systems? I found a great one for MySQL but sadly that doesn't do me
any good.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Backup Database Question

[Carlos Mennens]

So I'm looking to start regularly backing up my production database at
work. I'm tired of doing it manually every day before I go home. I use
the built in 'pg_dump' or 'pg_dumpall' utilities however I don't know
which is more beneficial for a nightly backup. Perhaps I should be
using the 'pg_dumpall' as a weekly / full backup only and not perform
this nightly but honestly I have no idea so I'm asking the experts
here. When should I use 'pg_dump' & 'pg_dumpall'? Is there a downside
to just backing up the entire database cluster nightly besides I/O
load and sacrificing system performance?

My last question is does anyone know how I can easily automate my
backups for PostgreSQL in Linux using Cron or some well written script
someone has on the web? I'm looking for anything  that can simplify
and automate my backups for me so I don't have to do them manually by
hand before I leave the office.

Thanks for any help in this area!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PostgreSQL Upgrade Procedure

[Carlos Mennens]

I've had PG 9.0 installed and working fine however it's Friday and I'm
running updates on the server & see that 9.1 is available. I know when
I upgrade, I will now have two instances of PostgreSQL installed under
/etc/postgresql:

Code:

slave:~# cd /etc/postgresql
slave:/etc/postgresql# ls -l
total 8
drwxr-xr-x 3 postgres postgres 4096 Aug 31 13:02 9.0
drwxr-xr-x 3 postgres postgres 4096 Sep  9 10:08 9.1

EndCode:

My question is what is the official procedure for removing the old
version and then running 9.1 only on my system. I don't want to leave
9.0 config files or directories so can someone please point me into
the clean and correct way of properly upgrading from 9.0 to 9.1? I did
do a pg_dump on my two databases & the default 'postgres' database and
backed them up so I can import them into the new 9.1 instance.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL 9.0 users

[Carlos Mennens]

I don't have bench marks but upgraded from 8.4 to 9.0 and it works perfect.
No performance issues or problems but I highly recommend 9.0.4!
On Jun 11, 2011 6:56 AM, "Zhidong She"  wrote:
> Hi all,
>
> Could you please give us some typical users that already upgraded to
> version 9.0?
> We have a debate internally on choosing 8.4 or 9.0 as our product
> backend database.
>
> And if you have any performance benchmark result, I will highly
appreciate.
>
> Many thanks,
> sheldon
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unable To Change Data Type

[Carlos Mennens]

On Fri, Jun 10, 2011 at 1:57 PM, Bill Moran  wrote:
> I don't think ALTER COLUMN TYPE will implicitly convert from varchar
> to INT.
>
> Try:
> ALTER TABLE reference
>  ALTER COLUMN color
>    TYPE INT
>    USING CAST(color AS INT);

Your command suggestion worked perfect but can you explain why yours
worked and mine didn't? I've never used 'USING CAST' command before.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Unable To Change Data Type

[Carlos Mennens]

For some reason I'm unable to change a column's TYPE from VARCHAR(20)
to INTERGER or SMALLINT. I'm required to note the manufactures color
code (value = 198) in the table data but keep getting this error and I
don't understand why:

The error I'm recieving is:

ERROR:  column "color" cannot be cast to type integer

The table is defined as such:

pearl=# \d reference
Table "public.reference"
 Column | Type  |   Modifiers
+---+
 id | integer   | not null default
nextval('reference_seq_id'::regclass)
 type   | character varying(20) | not null
 size   | smallint  | not null
 color  | character varying(20) | not null
 serial | integer   |
Indexes:
"reference_pkey" PRIMARY KEY, btree (id)
"reference_serial_key" UNIQUE, btree (serial)

The data in the database appears as such:

pearl=# SELECT id, color FROM reference ORDER BY id;
 id | color
+---
  1 | 198
  2 | 198
  3 | 198
  4 | 198
  5 | 198
  6 | 198
(6 rows)

Is this not possible to change the data type from VARCHAR to INTERGER
or something numeric since only manufacturer color codes will be
stored?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Remove Modifiers on Table

[Carlos Mennens]

On Tue, May 17, 2011 at 2:32 PM, Susan Cassidy  wrote:
> Don't forget to use setval to set the current value of the sequence to the 
> highest number used in the data already, so that the next insertion uses a 
> new, unused value.

Doesn't the SERIAL shortcut automatically do this on the fly? How
would I set this?

ALTER TABLE table_name ALTER COLUMN id SET DEFAULT nextval('foo_seq_id');

?

On Tue, May 17, 2011 at 2:33 PM, Adrian Klaver  wrote:
> It will work for an existing table if you are adding a column with 'type'
> SERIAL. You just cannot change an existing column to 'type' SERIAL.

Yup,

That's what I meant to say in a more clear and function statement ;)

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Remove Modifiers on Table

[Carlos Mennens]

On Tue, May 17, 2011 at 2:21 PM, Raymond O'Donnell  wrote:
> That's because of what I just mentioned above. :-) It's not a type: it's
> just a shortcut. What you need to do instead is something like this:
>
>  -- Create the sequence.
>  create sequence users_id_seq;
>
>  -- Tell the column to pull default values from the sequence.
>  alter table users alter column id set default nextval('users_id_seq');
>
>  -- Establish a dependency between the column and the sequence.
>  alter sequence users_id_seq owned by users.id;

Yup - that explains that the shortcut doesn't work for existing tables
but only during CREATE TABLE. Otherwise I will need to manually CREATE
SEQUENCE...blah blah blah.

Thank you!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Remove Modifiers on Table

[Carlos Mennens]

On Tue, May 17, 2011 at 12:57 PM, Carlos Mennens
 wrote:
> On Tue, May 17, 2011 at 12:38 PM, Raymond O'Donnell  wrote:
>> Yes, that's exactly right - SERIAL does it all for you. The mistake some
>> people make, on the other hand, is thinking that SERIAL is a type in its own
>> right - it's not, it just does all those steps automatically.

So if I have an existing column in my table with a INT data type, I
can't seem to understand how to convert this on my 8.4 production
server:

ALTER TABLE users ALTER COLUMN id TYPE SERIAL;
ERROR:  type "serial" does not exist

I verified from the docs that 8.4 does support SERIAL but how I
convert this data type, I can't seem to figure out. Below is my table
definition:

orlando=# \d users
Table "public.users"
 Column | Type  | Modifiers
+---+---
 id | integer   | not null
 fname  | character varying(40) | not null
 lname  | character varying(40) | not null
 email  | character varying(40) | not null
 office | character varying(5)  | not null
 dob| date  | not null
 title  | character varying(40) | not null
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"users_email_key" UNIQUE, btree (email)

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Remove Modifiers on Table

[Carlos Mennens]

On Tue, May 17, 2011 at 12:38 PM, Raymond O'Donnell  wrote:
> Yes, that's exactly right - SERIAL does it all for you. The mistake some
> people make, on the other hand, is thinking that SERIAL is a type in its own
> right - it's not, it just does all those steps automatically.

This information you have shed upon me makes my PG life so much easier!

It's amazing what you can do with information once you know it exist :p

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Remove Modifiers on Table

[Carlos Mennens]

On Tue, May 17, 2011 at 12:12 PM, Raymond O'Donnell  wrote:
> Well, the SERIAL pseudo-type creates the sequence, associates it with the
> column, and sets a DEFAULT on the column which executes the nextval()
> function on the sequence - all in one fell swoop. Read all about it here:
>
> http://www.postgresql.org/docs/9.0/static/datatype-numeric.html#DATATYPE-SERIAL

Wow I had no idea. So I do NOT need to manually create a sequence with:

CREATE SEQUENCE blah_id_seq;

And instead I can just use the SERIAL data type, unless I understood
that wrong. I'm going to read up on the URL you provided.

Thank you so much!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Remove Modifiers on Table

[Carlos Mennens]

On Tue, May 17, 2011 at 11:22 AM, Jaime Casanova  wrote:
> in postgres is as easy as
>
> CREATE TABLE test(
>  id SERIAL PRIMARY KEY);
>
> hey! it's even less keystrokes!

I don't understand how this command above is associated with being
able to auto increment the 'id' column. Sorry I'm still learning a
lot...

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Remove Modifiers on Table

[Carlos Mennens]

On Mon, May 16, 2011 at 4:58 PM, Bosco Rama  wrote:
> If you are truly intent on removing the sequence you'll need to do the
> following:
>
>   alter sequence users_seq_id owned by NONE
>   alter table users alter column id drop default
>   drop sequence users_seq_id

Yes that worked perfect! I'm just curious if I have 20 tables and then
want all the 'id' columns to be auto incrementing , that means I have
to have 20 listed sequences for all 20 unique tables? Seems very
cluttered and messy for PostgreSQL. Can one sequence be attributed to
multiple columns in multiple tables? I'm used to MySQL where this was
as easy as running:

CREATE TABLE test (
id INT PRIMARY KEY AUTO INCREMENT);

I guess  this is not the case in PostgreSQL, right?

Thank you!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Remove Modifiers on Table

[Carlos Mennens]

I created a modifier for auto incrementing my primary key as follows:

records=# \d users
Table "public.users"
 Column | Type  | Modifiers
+---+
 id | integer   | not null default
nextval('users_seq_id'::regclass)
 fname  | character varying(40) | not null
 lname  | character varying(40) | not null
 email  | character varying(40) | not null
 office | character varying(5)  | not null
 dob| date  | not null
 title  | character varying(40) | not null
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"users_email_key" UNIQUE, btree (email)

I recently tried to remove the modifier and it failed because it was
associated with the 'id' column so my question is how do I remove /
delete the modifier so I can delete the sequence I created to auto
increment my 'id' value? I don't want to drop the id column / loss my
column data, I just want to remove the associated modifier so I can
drop the sequence.

Thanks for any assistance.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Switching Database Engines

[Carlos Mennens]

On Sat, Apr 30, 2011 at 4:29 AM, Greg Smith  wrote:
> I wouldn't fight with this too much though.  Unless you have some really
> customized stuff in your wiki, there really is nothing wrong with the idea
> of dumping everything into XML, creating a blank PostgreSQL-backed MediaWiki
> install, then restoring into that.  That's what I always do in order to get
> a plain text backup of my server, and to migrate a wiki from one server to
> another.  There are all kinds of issues you could have left here before this
> works, trying to do a database-level export/reload--encoding, foreign key
> problems, who knows what else.  The database-agnostic export/import into XML
> avoids all of those.

Greg,

I'm with you and think that just doing an XML dump of the Wiki itself
is the best way to go. My question is when I do the XML dump as
follows:

/var/www/html/int/main/wiki/maintenance
[root@ideweb1 maintenance]# php dumpBackup.php --full > mw_wiki_2011_05_03.xml
PHP Warning:  PHP Startup: mcrypt: Unable to initialize module
Module compiled with module API=20050922, debug=0, thread-safety=0
PHPcompiled with module API=20060613, debug=0, thread-safety=0
[...]

So now I have a backup file of the Wiki on my old server running
MySQL. I have created the database and installed MediaWiki on the new
server using PostgreSQL as the backend. My question now is what are
your recommended steps in order to get the XML data imported on
MediaWiki using PostgreSQL? I know I also have to move the users since
the XML script / backup doesn't do anything in regards to the users.

Thanks again so much!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Switching Database Engines

[Carlos Mennens]

It seems that the 'mysql2postgres.pl' tool has instructions embedded
into the file so I ran the command as instructed to take the output
file and insert it into my PostgreSQL server and got the following
error message:

$ psql -p 5432 -h db1 -U wiki -f mediawiki_upgrade.pg
Password for user wiki:
BEGIN
SET
SET
SET
psql:mediawiki_upgrade.pg:25: ERROR:  relation "category" does not exist

Obviously this tool isn't written or supported by the Postgres
community but I figured I would ask in case someone understands this
and can advise me of a work around. If not it looks like this just
isn't going to work. :(

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Switching Database Engines

[Carlos Mennens]

I was able to export the Wiki database into a single file using the
conversion tool mentioned previously.

root@ideweb1 postgres]# ./mediawiki_mysql2postgres.pl --db=wiki
--user=mediawiki --pass=**
Writing file "mediawiki_upgrade.pg"

As you can see above that generated a new file in my current working
directory which is output of running the conversion tool:

[root@ideweb1 postgres]# ls -lh mediawiki_upgrade.pg
-rw-r--r-- 1 root root 112M Apr 27 09:24 mediawiki_upgrade.pg

So my question is now that I have the file above which I assume
contains the entire SQL database structure parameters and associated
data, how do I insert this into my existing PostgreSQL server? Do I
need to create a wiki database using template0 or template1? Or should
I just create a database as normal w/o templates and assign a wiki
role to that database?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Switching Database Engines

[Carlos Mennens]

On Tue, Apr 26, 2011 at 4:34 PM, Alban Hertroys
 wrote:
> I don't know much about mediawiki (except for how to use it), but it's not 
> unusual for modern web-apps to have some functionality to dump their contents 
> in a consistently formatted file (often XML) that it can subsequently import 
> into a new environment. Might be worth looking into.

Yes MediaWiki can dump pages into XML but that's what scares me. It
does it in pages so I would have to dump every Wiki page into a
separate XML file rather than doing one huge Wiki dump. I guess I need
to check the MediaWiki forums and find out how I can export everything
into XML. But lets say I do export every thing to XML. Now I have XML
file(s) and a new database for MediaWiki. How do I get all the old
data on the new server? Do I do a fresh install 1st and let the
installer configure my database as the assigned role, then import the
XML data through the MediaWiki tool? I guess I should check their
forums.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Switching Database Engines

[Carlos Mennens]

On Tue, Apr 26, 2011 at 3:06 PM, Greg Sabino Mullane  wrote:
> Correct. Keep in mind I don't think the XML route will convert the users
> table, just the wiki data itself. As someone else mentioned, the
> wiki itself will work fine, but support for any MediaWiki extensions
> is hit or miss because MediaWiki was a MySQL only shop for so long
> (and Wikimedia still uses it, and they influence a lot of the MW
> development).

So what is the ideal or best suggested approach on tackling this task?
I built the new Apache server and installed the new (latest) version
of MediaWiki on it. I then pointed the installation of the new server
to my existing PostgreSQL database server. I created a user and
database for MediaWiki to use but it self generated a new table schema
during the installation. Should I proceed or do I somehow need to
blast what the new installation did and migrate my old data in it's
place. I guess there just isn't a clean understanding on my part on
what I need to do in what particular order.

I tried running the tool as suggested before and unless I'm doing
something wrong, I have no idea why it didn't work:

[root@db_old postgres]# pwd
/var/www/html/int/main/wiki/maintenance/postgres

[root@db_old postgres]# ls -l
total 60
drwxr-xr-x 2 root root  4096 Jul 13  2009 archives
-rw-r--r-- 1 root root 13988 Mar 12  2009 compare_schemas.pl
-rw-r--r-- 1 root root 14063 Nov 22  2008 mediawiki_mysql2postgres.pl
-rw-r--r-- 1 root root 23596 Mar 19  2009 tables.sql

[root@db_old postgres]# sh mediawiki_mysql2postgres.pl
mediawiki_mysql2postgres.pl: line 12: use: command not found
mediawiki_mysql2postgres.pl: line 13: use: command not found
mediawiki_mysql2postgres.pl: line 14: use: command not found
mediawiki_mysql2postgres.pl: line 15: use: command not found
mediawiki_mysql2postgres.pl: line 17: syntax error near unexpected token `('
mediawiki_mysql2postgres.pl: line 17: `use vars qw(%table %tz %special
@torder $COM);'

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Switching Database Engines

[Carlos Mennens]

On Tue, Apr 26, 2011 at 1:44 PM, Greg Sabino Mullane  wrote:
> In general, yes. For your specific use case, it might be best to use
> MediaWiki's XML dump and restore. You could also use the conversion
> script that comes with MediaWiki, at:
>
> maintenance/postgres/mediawiki_mysql2postgres.pl
>
> It's a little old so I can't promise it will work with recent versions
> of MediaWiki, but should be enough to get you started testing.

Just to be clear and make sure I understand correctly, I can export
the Wiki info using the MediaWiki XML export tool (which I found) or I
can try to use the MediaWiki tool referenced as
'mediawiki_mysql2postgres.pl', right? I think from reading the
options, I should try the XML export 1st.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Switching Database Engines

[Carlos Mennens]

We've been using a Wiki server at the office for years. It was
originally configured to use MySQL and finally after 8+ years we're
moving the Wiki to a new platform of hardware. My question is the Wiki
software (MediaWiki) is the only thing still tied to and using MySQL
which we want to decommission but we've been using it for years so I'm
worried we will lose the data. I've done some Google'ing to find out
how can I change the MySQL database dump and successfully export it
into my new PostgreSQL database however I don't know how practical or
recommended this process is. I found sites like the following:

http://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL

Can you guys tell me if this is something that will work? I don't mean
the exact link above but just in general taking a database from MySQL
and successfully migrating it for PostgreSQL use?

>From what I can see in the MySQL database, there appears to be 43
tables with lots of column data and who knows what else:

mysql> show tables;
+--+
| Tables_in_wiki   |
+--+
| dp_archive   |
| dp_category  |
| dp_categorylinks |
| dp_change_tag|
| dp_externallinks |
| dp_filearchive   |
| dp_hitcounter|
| dp_image |
| dp_imagelinks|
| dp_interwiki |
| dp_ipblocks  |
| dp_ipblocks_old  |
| dp_job   |
| dp_langlinks |
| dp_logging   |
| dp_math  |
| dp_objectcache   |
| dp_oldimage  |
| dp_page  |
| dp_page_props|
| dp_page_restrictions |
| dp_pagelinks |
| dp_protected_titles  |
| dp_querycache|
| dp_querycache_info   |
| dp_querycachetwo |
| dp_recentchanges |
| dp_redirect  |
| dp_revision  |
| dp_searchindex   |
| dp_site_stats|
| dp_tag_summary   |
| dp_templatelinks |
| dp_text  |
| dp_trackbacks|
| dp_transcache|
| dp_updatelog |
| dp_user  |
| dp_user_groups   |
| dp_user_newtalk  |
| dp_valid_tag |
| dp_validate  |
| dp_watchlist |
+--+
43 rows in set (0.01 sec)

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Rename or Re-Create Constraints?

[Carlos Mennens]

On Sat, Apr 9, 2011 at 12:58 PM, Tom Lane  wrote:
> ALTER INDEX accounts_pkey RENAME TO whatever
>
> On very old versions of PG you may have to spell that "ALTER TABLE"
> instead of "ALTER INDEX", but it's the same thing either way.

Thank you so much for clearing that up for me Tom! I just couldn't
find anything documented or do I understand SQL enough to work through
that w/o an example.

I read the PostgreSQL documentation all morning and just couldn't find
it. Also to make sure I did this correct, if I had an existing table
w/o a PRIMARY KEY index / constraint, is the following correct?

CREATE UNIQUE INDEX users_pkey ON public.users (id);
CREATE INDEX

I'm guessing that's how I generate a index / constraint on an existing
table when it was generated during the table creation SQL command,
right?

Is there a difference between an INDEX and a CONSTRAINT?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Rename or Re-Create Constraints?

[Carlos Mennens]

On Fri, Apr 8, 2011 at 8:35 PM, Tom Lane  wrote:
> I believe you can rename the underlying indexes and the constraints will
> follow them.  (This works in HEAD anyway, not sure how far back.)

Below is my table:


inkpress=# \d marketing
  Table "public.marketing"
 Column  | Type  | Modifiers
-+---+---
 id  | integer   | not null
 vendor  | character varying(40) | not null
 account | integer   | not null
 email   | character varying(40) | not null
 state   | character(2)  | not null
Indexes:
"accounts_pkey" PRIMARY KEY, btree (id)
"accounts_account_key" UNIQUE, btree (account)
"accounts_email_key" UNIQUE, btree (email)
"accounts_vendor_key" UNIQUE, btree (vendor)

I renamed the table name from 'accounts' to 'marketing' however all
the constraints listed under 'indexes' are still named 'accounts_*'
and I've tried to rename them but I can't find any information with an
example command to rename the constraints:

ALTER TABLE marketing ...???

I can't find any update / alter SQL commands to correct the constraint
inconsistency.

:(

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Rename or Re-Create Constraints?

[Carlos Mennens]

On Fri, Apr 8, 2011 at 8:35 PM, Tom Lane  wrote:
> I believe you can rename the underlying indexes and the constraints will
> follow them.  (This works in HEAD anyway, not sure how far back.)

I'm sorry but I don't understand what that means or how to relate that
to a SQL command to rename the constraint. Do you have an example of
how that command would look?

>> 2. When renaming the table, is there a way to rename both the table
>> and all associated constraints?
>
> No, there's nothing automatic for that.  IIRC there used to be code to
> try to do this when you renamed a single column; but we took it out,
> probably because it risked unexpected failures due to index name
> collisions.
>
>                        regards, tom lane
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Rename or Re-Create Constraints?

[Carlos Mennens]

I've searched and really can't find a definitive example or someone
renaming a constraint. I renamed a table yesterday and noticed that
the constraint name was still named the old table name:

inkpress=# ALTER TABLE accounts RENAME TO fashion;
ALTER TABLE

inkpress=# \d fashion
   Table "public.fashion"
 Column  | Type  | Modifiers
-+---+---
 id  | integer   | not null
 vendor  | character varying(40) | not null
 account | integer   | not null
 email   | character varying(40) | not null
 state   | character(2)  | not null
Indexes:
"accounts_pkey" PRIMARY KEY, btree (id)
"accounts_account_key" UNIQUE, btree (account)
"accounts_email_key" UNIQUE, btree (email)
"accounts_vendor_key" UNIQUE, btree (vendor)

1. Do I need to remove all the table constraints or is there a way to
rename them?

2. When renaming the table, is there a way to rename both the table
and all associated constraints?

I've looked over the following guide and am more confused than ever:

http://www.postgresql.org/docs/8.1/static/sql-altertable.html

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Changed SSL Certificates

[Carlos Mennens]

On Fri, Apr 8, 2011 at 2:01 PM, Adrian Klaver  wrote:
> Per here:
> http://www.postgresql.org/docs/8.4/static/ssl-tcp.html
> File    Contents        Effect
> server.crt      server certificate      requested by client
> server.key      server private key      proves server certificate sent by
> owner; does not indicate certificate owner is trustworthy
> root.crt        trusted certificate authorities checks that client
> certificate is signed by a trusted certificate authority
> root.crl        certificates revoked by certificate authorities client
> certificate must not be on this list
>
> Rename your certs to above.

Oh I mis-understood. I just need to rename my symbolic links, not my
actual certificate file names. Changed symbolic link names and
everything is happy again.

Thanks so much for everyones help!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Changed SSL Certificates

[Carlos Mennens]

On Fri, Apr 8, 2011 at 1:15 PM, Diego Schulz  wrote:
> Hi,
> When linking to the certificate and key you should specify the full path.
> ln -s /etc/ssl/certs/db1_ssl.crt      /full/path/to/db1_ssl.crt
> ln -s /etc/ssl/private/db1_ssl.key   /full/path/to/db1_ssl.key

Thanks for the quick reply Diego. I posted the commands above and I
used the full path to the certificates as you can see. Here's the
info:

lrwxrwxrwx 1 postgres postgres   26 Apr  8 10:43 db1_ssl.crt ->
/etc/ssl/certs/db1_ssl.crt
lrwxrwxrwx 1 postgres postgres   28 Apr  8 10:50 db1_ssl.key ->
/etc/ssl/private/db1_ssl.key

The 1st part is just the symbolic link referenced in
/var/lib/postgresql/8.4/main but you can see it knows to reference the
symbolic links to /etc/ssl/...

I'm thinking there's some random configuration file for PostgreSQL
that has pointers to the old server.crt and server.key files but I've
searched /etc/postgres/ and /var/lib/postgresql/8.4/main completely
and can't find it what so ever. I am not authorized to disable SSL per
DoD standards / requirements sadly.

Any thing else I am missing? I can't be the 1st person to switch SSL
certificates during utilization.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Changed SSL Certificates

[Carlos Mennens]

I had self signed SSL certificates on my database server but since
then removed them and received updated certificates from the security
team. I removed (backedup) the old server.crt & server.key and now
have db1_ssl.crt & db1_ssl.key in the identical location as the old
SSL certificates. I then went to /etc/postgres/8.4/main and removed
the old symbolic links for the old certificates and generated new
symbolic links:

ln -s /etc/ssl/certs/db1_ssl.crt db1_ssl.crt
ln -s /etc/ssl/private/db1_ssl.key db1_ssl.key

I then restarted PostgreSQL and got the following error:

2011-04-08 09:54:34 EDT FATAL:  could not load server certificate file
"server.crt": No such file or directory
2011-04-08 10:00:43 EDT FATAL:  could not load server certificate file
"server.crt": No such file or directory

I looked for anywhere else in /var/lib/postgres/ & /etc/postgres/ but
can't find anything else that's calling the old certificates. I
changed the ownership on the certificates and symbolic links to either
root or postgres and nothing worked. It fails to start with the
following error:


root@db1:/# /etc/init.d/postgresql start
Starting PostgreSQL 8.4 database server: mainThe PostgreSQL server
failed to start. Please check the log output: 2011-04-08 12:36:54 EDT
FATAL: could not load server certificate file "server.crt": No such
file or directory ... failed!

I checked the documentation page:

http://www.postgresql.org/docs/8.4/static/libpq-ssl.html

Table 30-4. Libpq/Client SSL File Usage

FileContentsEffect
~/.postgresql/postgresql.crtclient certificate  requested by server
~/.postgresql/postgresql.keyclient private key  proves client
certificate sent by owner; does not indicate certificate owner is
trustworthy
~/.postgresql/root.crt  trusted certificate authorities checks server
certificate is signed by a trusted certificate authority
~/.postgresql/root.crl  certificates revoked by certificate
authorities server certificate must not be on this list

Can anyone tell me what I'm doing wrong or missing here? I can't
disable SSL per DoD requirements sadly.

-Carlos

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Auto Adjust Age

[Carlos Mennens]

On Wed, Apr 6, 2011 at 11:24 AM, JC de Villa  wrote:
> Theres also the age() function
>
> SELECT age(dob);
>
> Should give you
>
>           age
> -
>  31 years 5 mons 17 days
>
> If you want to be really exact about it. :)

That worked awesome too!

ide=# SELECT age(dob) FROM users;
   age
--
 31 years 10 mons 12 days
(1 row)

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Auto Adjust Age

[Carlos Mennens]

On Wed, Apr 6, 2011 at 11:20 AM, Andrew Sullivan  wrote:
> Why do you have the age stored at all?  When you SELECT from the table
> and want someone's age, just do
>
>    SELECT [. . .], extract('years' from age(CURRENT_TIMESTAMP,dob)) as age
>           . . . FROM users . . .
>
> By and large, it's not a good idea to store something you can
> calculate from other data you have.

I'm very sorry as I didn't know PG or SQL could auto calculate age
with existing parameters. I'm trying to learn as much SQL as I can
during my spare time. I tried the following and it worked great!

ide=# SELECT extract('years' from age(CURRENT_TIMESTAMP,dob)) as age
FROM users;
 age
-
  31
(1 row)


THANK YOU;

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Auto Adjust Age

[Carlos Mennens]

I've only been using PostgreSQL since Oct 2010 and it's my first
experience with SQL or any ORDBMS. I've searched on the web and been
creating my own database users, databases, tables from scratch which
has been interesting to say the least but now I would like to know if
this is possible in SQL or PostgreSQL since I can't find anything
online that shows me how to do so. I've created a table called 'users'
and I have it configured as follows:

CREATE TABLE users
(
   id integer PRIMARY KEY UNIQUE NOT NULL, --ID
   fname character varying(40) NOT NULL, --First name
   lname character varying(40) NOT NULL, --Last name
   email character varying NOT NULL, --email address
   office integer NOT NULL, --Office number
   dob date NOT NULL, --Date of birth
   age integer NOT NULL --Age
)
;

Is there a way in SQL I can have the users 'age' be auto adjusted
based on the 'id' & 'dob'? I would assume this is possible because if
you have 100 employees, I doubt someone has time to sit and change
everyone's age from 31 > 32 on their birthday. Can someone please help
explain how this works or what the SQL code would look like assuming
that it's possible? I have no advanced far enough to see what triggers
and views are so perhaps it's just my level with SQL in general.

Thank you so much.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL ODBC Driver Help

[Carlos Mennens]

On Tue, Feb 15, 2011 at 12:46 PM, A.M.  wrote:
> I googled "connect excel postgresql" and found this:
>
> http://port25.technet.com/videos/research/excelopendbprimer.pdf
>
> which seems to take one through all the steps.

I have been using that actual .PDF as a guide and I guess it's
different on Office 2007 than show on that site using Office 2003. I
guess I will keep searching to find out what I need to do in order to
connect Office 2007 Excel or Access to PostgreSQL.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PostgreSQL ODBC Driver Help

[Carlos Mennens]

I'm trying to figure out how I can have users in the office connect
their Microsoft Office 2007 clients to our company database server
running PostgreSQL 8.4.7. I've configured PostgreSQL to accept
incoming connections and allow users to login however I read that I
need to have each client install a MSI pack that allows ODBC drivers
to talk to the database server. I've installed the ODBC driver from
the following URL:

The Drivers can be found at:
PostgreSQL: http://www.postgresql.org/ftp/odbc/versions/msi/


I installed it on the machine running Office 2007 but beyond that I
have no idea how to initialize a connection from Excel or Access to
the database server. Can anyone please help me figure out how to
connect?

I have my 'pg_hba.conf' & 'postgresql.conf' files configured perfectly
to accept incoming SSL connections from my internal network on the
default port for PostgreSQL.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Storing Media Types

[Carlos Mennens]

I've created a basic table called 'employees' & I've been asked to
store a profile photo of all employees. I've looked on Google and the
9.0 documentation but can't find any clear instructions on how I would
be to insert photo's stored in a local directory on the server
(/var/lib/postgres/data/media/pics). I'm trying to understand how I
would create an entry into the table I show below to be able to add
photo's for each user. Does anyone have an example of what the code
would look like and or offer any assistance?


ghost=> CREATE TABLE employees
(
id INT PRIMARY KEY NOT NULL UNIQUE,
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
email VARCHAR(50) UNIQUE NOT NULL,
branch VARCHAR(50) NOT NULL,
position VARCHAR(50) NOT NULL,
office INT NOT NULL,
dob DATE NOT NULL,
photo ?
)
;
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"employees_pkey" for table "employees"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index
"employees_email_key" for table "employees"
CREATE TABLE

-Carlos

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] No Password Access

[Carlos Mennens]

Today on a new PostgreSQL 9.0.3 server I created a new user:

CREATE ROLE carlos LOGIN CREATEDB CREATEROLE;
CREATE ROLE

I then set a password and comment on the user:

ALTER ROLE carlos WITH PASSWORD 'letmein';
ALTER ROLE

COMMENT ON ROLE carlos IS 'Database Administrator';
COMMENT

So I now try to connect to the database from my desk workstation:

carlos@laptop:~$ psql -h db1 -d postgres
psql (8.4.7, server 9.0.3)
WARNING: psql version 8.4, server version 9.0.
 Some psql features might not work.
Type "help" for help.

postgres=> SELECT current_user;
 current_user
--
 carlos
(1 row)

postgres=> \du
List of roles
 Role name | Attributes  | Member of
---+-+---
 carlos| Create role | {}
   : Create DB
 maggie| Create DB   | {}
 postgres  | Superuser   | {}
   : Create role
   : Create DB

Why am I not prompted for a password when I connect from my laptop to
the server? I didn't grant attributes of 'INHERIT' to carlos so
shouldn't I be prompted for my password? Am I missing something here?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Additional Grants To SuperUser?

[Carlos Mennens]

On Fri, Feb 4, 2011 at 5:08 PM, Dmitriy Igrishin  wrote:
> These all (SUPERUSER, CREATEDB, SUPERUSER) are role attributes.
> By performing ALTER ROLE postgres NOSUPERUSER it is possible to
> turn role with a superuser status into a role that just can create databases
> and manage roles (admin, but without superuser privileges).

So is it very bad to alter ANY of the default role attributes granted
to the 'postgres' user? I don't know if removing role attributes from
him will have negative consequences to features / functional tasks of
the PostgreSQL server / client application(s).

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Additional Grants To SuperUser?

[Carlos Mennens]

On Fri, Feb 4, 2011 at 2:18 PM, David Johnston  wrote:
> Not to be smart about it but you could just logon as carlos (or a different
> superuser you create for this purpose) and issue "Create Database xxx" and
> "Create Role xxx" statements and see whether they work.  A superuser should
> (imo) be able to do everything (including dropping) without any additional
> permissions required so unless you see that carlos cannot I would say you
> are good.

Yes but I'm trying to understand the difference because the default
'postgres' user that is auto-configured to have 'SUPERUSER',
'CREATEDB', & 'CREATEROLE' grants. I'm trying to understand if those
are redundant grants or if there is a reason PostgreSQL developers
grant the 'postgres' user with SUPERUSER, CREATEDB, & CREATEROLE.
Seems to me logically that if a someone is a superuser, then they
should be able to CREATEDB & CREATEROLE, no? So why would the
'postgres' user need those additional attributes?


postgres=# \du
List of roles
 Role name  | Attributes  | Member of
+-+---
 cmennens   | Superuser   | {}
 postgres   | Superuser   | {}
: Create role
: Create DB

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Additional Grants To SuperUser?

[Carlos Mennens]

I created a role named 'carlos' which is my current user account with
'superuser' grants but my question is when I look at 'postgres'
account, he has additional grants that I don't understand.

List of roles
 Role name | Attributes  | Member of
---+-+---
 carlos   | Superuser   | {}
 jmadeline  | Create DB   | {}
 mwilshaw  | Create DB   | {}
 postgres| Superuser   | {}
   : Create role
   : Create DB

So from what I see above, 'carlos' is a superuser but do I need to
grant him 'CREATEROLE' & 'CREATEDB' rights along with 'SUPERUSER' or
is 'SUPERUSER' by itself good enough?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Remove Role Membership

[Carlos Mennens]

I've been searching the documentation and I've tried ALTER ROLE,
REVOKE, etc etc etc & can't seem to find anything that shows me how to
remove membership roles from a particular user / role. I've granted a
user name 'david' a member of 'finance' role but how do I remove the
role membership from 'david'?

Sorry for sounding completely dumb but I just can't seem to find this
in the documentation.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Database Design Question

[Carlos Mennens]

Thanks for all the suggestions and everyone appears to agree that if
the applications don't need to share data, then I should split them up
into separate database and nothing more.

I appreciate your input and explanations as well.

-Carlos

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Database Design Question

[Carlos Mennens]

I was sitting down thinking the other day about when is it good to
generate a new database or just use an existing one. For example, lets
say my company name is called 'databasedummy.org' and I have a
database called 'dbdummy'. Now I need PostgreSQL to manage several
applications for my company:

- webmail
- software
- mediawiki
- phpbb forum

Now what I've been doing is just creating multiple tables in the
'dbdummy' database but each table is owned by different users
depending on their role. Is this bad? Should I be creating new
databases for each application above rather than one single company
database?

Just trying to understand good DBA design practice. This is obviously
a very general question but any feedback on what good or bad issues
would come from me dumping all my tables for applications in one
database or spread out across multiple databases on PostgreSQL.

Thank you!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] linux server configuration

[Carlos Mennens]

On Thu, Jan 6, 2011 at 6:20 AM, Sim Zacks  wrote:
> We are about to build a new database server, our plan is to use Debian.
>
> Is there documentation of recommended server configurations for Linux, such
> as kernel parameters, preferred file system, etc that work best with
> postgresql?
>
> I'm not talking about the pg configuration, which I have seen a lot of
> documentation about, more on getting the OS ready.

My company has two identical PostgreSQL servers running on Debian
(Squeeze) & we didn't tune the kernel and left it as it was installed
by Debian. I partitioned the drives as 'ext4' & and the data is stored
on a iSCSI NAS (RAID 5) configuration. The servers have been rock
solid. Just be super careful that you don't blindly upgrade the server
(using apt-get upgrade) and accidentally swap from 8.4 > 9.0. I ran
into this problem and it was very messy. This is a PG issue however,
not a Debian issue. On a side note I've ran PostgreSQL 8.4 in a
production environment on the following:

- CentOS 5 64-bit
- RHEL 5 64-bit
- Arch Linux 64-bit
- Ubuntu 10.04 Server
- Slackware Linux 13 64-bit
- Gentoo Linux (Uggh) 64-bit

Out of all those distributions, I can honestly say that between Arch
Linux and Debian, no other distribution comes close.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Rename Schema Removes Unique Constraints?

[Carlos Mennens]

I decided last night to rename my 'public' schema (Not sure of that's
a good / bad idea) since I'm still learning about how schema's work on
PostgreSQL. My question is:

1. If I have a constraint (specifically 'unique') on a specific table,
when I rename the public schema, does that impact my ''unique', 'not
null', and 'primary key' constraints?

2. When you install a new PostgreSQL server at home or work, do you
guys generally leave the default 'public' schema or do you at some
point rename or use new schema for reasons I'd like to understand. I
don't understand how schema's really fit into the picture but I'm
interested in knowing what others do with their default schema.

Thanks for the help!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Understanding Roles & Grant Options

[Carlos Mennens]

I've been reading the documentation and I'm trying to understand what
'GRANT' options make up a 'superuser' in PostgreSQL.

I've got my account which is 'carlos' and then I have an account
called 'jason'. Can someone please explain the difference between the
two roles:

postgres=# \dg
List of roles
 Role name |  Attributes  | Member of
---+--+---
 carlos  | Superuser| {it}
 it| Cannot login | {}
 jason | Create role  | {it}
   : Create DB
 postgres  | Superuser| {}
   : Create role
   : Create DB

Obviously 'Carlos' is a superuser but what does that exactly entail
beyond CREATEDB & CREATEROLE?

http://www.postgresql.org/docs/8.1/static/app-createuser.html

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres forums ... take 2

[Carlos Mennens]

On Mon, Dec 20, 2010 at 7:26 PM, Thom Brown  wrote:
> I know this topic has gone quiet, I still think it's worth investing
> time and resources in.  I don't expect any progress to be made until
> the new year now, but I hope we can continue this after the Christmas
> period is over.
>
> And nice work collating the discussions so far onto the wiki.  That
> should make it easier for everyone to keep up with developments :)

I would like to be more involved in this! I've got lots of time to
devote and available resources so let me know what is needed. I would
like to see this grow into a full dedicated "official" web forums.

-Carlos Mennens

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Role Membership

[Carlos Mennens]

On Mon, Dec 20, 2010 at 1:32 PM, Scott Marlowe  wrote:
> No user, no group, they're al roles.  Roles are both / either.

Ah now I understand. Thank you!

> You grant them that:
>
> grant rolename to username;
>
> Then you only ever have to grant / revoke a role to change
> permissions, no need to do a million grants all over the place on each
> table.  Just grant it once to the role, grant the role to the user,
> viola, you're done.

OK I now understand:

postgres=# \l
  List of databases
   Name|   Owner   | Encoding |  Collation  |Ctype|
Access privileges
---+---+--+-+-+---
 caldega   | cmennens  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 tiburon   | cmennens  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 ide   | cmennens  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 orlando   | jmadeline | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 software  | mwilshaw  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=c/postgres  +
   |   |  | | |
postgres=CTc/postgres
 template1 | postgres  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=c/postgres  +
   |   |  | | |
postgres=CTc/postgres
(8 rows)

postgres=# ALTER DATABASE ide OWNER TO it;
ALTER DATABASE

postgres=# \l
  List of databases
   Name|   Owner   | Encoding |  Collation  |Ctype|
Access privileges
---+---+--+-+-+---
 caldega   | cmennens  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 tiburon   | cmennens  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 ide   | it| UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 orlando   | jmadeline | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 software  | mwilshaw  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=c/postgres  +
   |   |  | | |
postgres=CTc/postgres
 template1 | postgres  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=c/postgres  +
   |   |  | | |
postgres=CTc/postgres

Thanks for helping me out!

-Carlos

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Role Membership

[Carlos Mennens]

On Mon, Dec 20, 2010 at 12:05 PM, Scott Marlowe  wrote:
> Odd, mine does.  Got a complete example of creating a role and not seeing it?
>
> Here's mine:
> smarlowe=# create role stans;
> CREATE ROLE
> smarlowe=# \dg
>            List of roles
>  Role name |  Attributes  | Member of
> ---+--+---
>  postgres  | Superuser    | {}
>           : Create role
>           : Create DB
>  smarlowe  | Superuser    | {}
>           : Create role
>           : Create DB
>  stans     | Cannot login | {}

I guess I am still confused by role / group & user accounts. If you
create a role / group called 'finance', it then shows up as a user
when I do \dg? Then how do I make users a member of the 'finance' role
/ group if they're listed just like regular users are?

easports=# CREATE ROLE finance;
CREATE ROLE
easports=# \dg
   List of roles
 Role name |Attributes | Member of
---+---+---
 cmennens  | Superuser | {}
 finance   | Cannot login  | {}
 postgres  | Superuser, Create role, Create DB | {}

From the above listing, I would expect 'finance' to not be listed with
my users since finance is a role / group, not a single user. I want to
make specific users members of 'finance'. Am I missing something or
just slow today?

>> 2. How to see which 'users' are all members of 'accounting'? Would
>> that be done simply with '\dg'?
>
> Yeah.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Role Membership

[Carlos Mennens]

On Mon, Dec 20, 2010 at 10:41 AM, Adrian Klaver  wrote:
> Roles = users/groups. In older versions there where users and groups, that has
> been consolidated into the concept of a role. If it makes it easier I use the
> concept of roles with login privileges as a users and roles without as groups.

So I did a \dg & a \du according to '\?' & I can't see the difference
between the two commands. One is listed as showing 'users' and the
other for 'groups' but the output looks identical to me. Perhaps my
database is not that robust yet.

So if I wanted to create a group / role for accounting, would I simply just do:

CREATE ROLE accounting;

My question is I can't find:

1. How to view all previously created roles on my database. '\dg'
doesn't show me the new role I created above.
2. How to see which 'users' are all members of 'accounting'? Would
that be done simply with '\dg'?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Role Membership

[Carlos Mennens]

I was looking at my users and realized none of my users are members of
a specific group or role. Not sure if there's a difference between the
two (role / group) in PostgreSQL, is there?

easports=# \du
   List of roles
 Role name |Attributes | Member of
---+---+---
 carlos  | Superuser | {}
 postgres  | Superuser, Create role, Create DB | {}

Now my question is about the section 'Member of' and how this is
commonly utilized by most PG administrators. Is this the same thing as
explained here:

http://www.postgresql.org/docs/8.2/interactive/role-membership.html

If I am not mistaken I can simply create a role called 'accounting'
and add several users to the 'accounting' group rather than juggling
several dozen user grants, correct? If anyone can please tell me if
I'm hot or cold on this issue as well as anything you would think is
helpful for me to know that is not in the documentation that you
learned as a PG administrator.

Thanks so much!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] What Programs Do You Use For PG?

[Carlos Mennens]

I'm just wondering what programs you guys / girls are using PostgreSQL
for. So far I've installed PG 9 on my Debian Linux server and manually
created all my databases, schema's, and tables for my personal email /
address book. It's very basic and small but I was wondering if you
guys know of any programs that I can install that I could beef up my
PG database with? Just looking for basic applications that use PG as a
back-end so I can see how different programs are developed to create
tables and schema's. I tried looking on line for an open source email
address book that would use PostgreSQL as a back end but couldn't find
one. Anyone have any recommendations?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Understanding Schema's

[Carlos Mennens]

On Tue, Dec 14, 2010 at 7:17 PM, Joshua D. Drake  wrote:
> You can cross query a schema but not a database.
>
> So you can create:
>
> create table fire.foo()
> create table ice.foo()
>
> And they are isolated from each other physically and logically but you
> can query them both:
>
> SELECT fire.*, ice.* join on (id)

Why would anyone in a random scenario want to have independent
schema's to cross query? I'm just trying to see how this would be
useful in any scenario.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Understanding Schema's

[Carlos Mennens]

I've recently switched from MySQL & have read the documentation for
'schema's' however I guess I'm just not at that level or really daft
when it comes to database design.

http://www.postgresql.org/docs/current/static/ddl-schemas.html

I'm trying to understand the relation between actual databases &
tables but can't grasp the relation with schema's so I was wondering
if someone has an easy way of explaining this beyond the documentation
I linked above. I notice my fresh 9.0 install has a default schema
called 'public' which every newly database I create defaults to and I
also created two new schema's called 'fire' & 'ice' but from what I
have written above, obviously I don't have any understanding of how
they work.

I greatly appreciate any info and or help since I appear to be lost.

-Carlos

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 8.4 Data Not Compatible with 9.0.1 Upgrade?

[Carlos Mennens]

On Tue, Nov 2, 2010 at 10:53 AM, Steve Clark  wrote:
> mv /var/lib/postgres/data  /var/lib/postgres/data.old

Before I move or rename '/var/lib/postgres/data', what version of
PostgreSQL should I be at? 8.4 or 9.0?

> You will then have to do an initdb to create the basic 9.x databases.
> You can then use psql or pg_restore depending on how you dumped
> your data to restore your databases.

I simply ran the following command:

/usr/bin/pg_dump finance > finance.sql

> With fedora you use either:
> /etc/init.d/postgresql initdb
> or
> service postgresql initdb
> to initialize the 9.x database system.

Sadly that command didn't pan out for Arch Linux:

# /etc/rc.d/postgresql initdb
usage: /etc/rc.d/postgresql {start|stop|restart}

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 8.4 Data Not Compatible with 9.0.1 Upgrade?

[Carlos Mennens]

So I am still in the dark about the entire upgrade or step up process
from 8.4.4-6 to 9.0.1-2. I have my 4 databases all backed up which I
did when my server was 8.4.4-6 using the 'pg_dump' utility. That
worked fine. So after I backed up my databases, I then upgraded the
daemon to 9.0.1-2 and from there I don't understand the process. I
obviously can't connect to the PostgreSQL database after I upgrade
because I get the error:

FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 8.4,
which is not compatible with this version 9.0.1.

I have located the '/usr/bin/pg_upgrade' but I don't understand how I
go about this. I am worried because I have 12 unique database users
with unique / individual grants on specific databases. I don't want to
start from scratch so I would assume this is where the 'pg_upgrade'
script comes into play. How do I proceed from this point on? Obviously
I can't access or connect to PostgreSQL once I upgrade to 9.0.1-2 so I
assume at this time I need to execute the 'pg_upgrade' script to move
forward, right?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 8.4 Data Not Compatible with 9.0.1 Upgrade?

[Carlos Mennens]

On Mon, Nov 1, 2010 at 2:27 PM, Steve Crawford
 wrote:

> I'm guessing you are missing an initdb. Move your old data directory
> somewhere else for now and do a new initdb so you can start up version 9.

When you say 'old data' can you be more specific as to the path and
possible files I need to move?

I go to '/var/lib/postgres/data/' directory however I am not sure
where from that folder structure I need to start moving files away
without breaking basic server functionality & connection data.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 8.4 Data Not Compatible with 9.0.1 Upgrade?

[Carlos Mennens]

On Mon, Nov 1, 2010 at 2:00 PM, Bill Moran  wrote:
> To clarify my earlier comments, if you're going to use pg_upgrade, you
> probably won't need to downgrade to 8.4.  My comments about putting
> 8.4 back on would have be necessary if you were going to go the old
> dump/restore route.

I've already downgraded / dumped the databases and upgraded to the
latest version. I was then going to create the new databases however I
can't connect because of the invalid data error:

I guess I'm just missing something here...I didn't choose to go with
the 'pg_upgrade' script simply because I wasn't aware of it's location
and was worried it would dork up my data so I reverted back to 8.4.4-6
and got a clean backup of my databases. So is there nothing I can do
from my position now?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 8.4 Data Not Compatible with 9.0.1 Upgrade?

[Carlos Mennens]

On Mon, Nov 1, 2010 at 1:32 PM, Scott Marlowe  wrote:
> you would do it with 9.0.x installed, and there should be a program in
> one of the 9.0 packages that has pg_upgrade in it.

So I have my 8.4.4-6 databases backed up. I don't know if I needed the
default 'postgres' database dumped but I did that one too just in
case. I then upgraded the server to 9.0.1-2 and my question is how do
you create a new database in PostgeSQL 9.0 coming from 8.4.4-6 when
the server refuses to start. I can't connect to PostgreSQL simply
because the logs tell me the data is not compatible. Am I missing
something?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 8.4 Data Not Compatible with 9.0.1 Upgrade?

[Carlos Mennens]

On Mon, Nov 1, 2010 at 12:52 PM, Richard Broersma
 wrote:

> oops: http://www.postgresql.org/docs/9.0/interactive/pgupgrade.html

Thanks for the URL. I will try this but I am confused how to proceed?
Can I attempt this with PostgreSQL 9.0.1-2 server installed and the
data is still 8.4 or do I need to find a way to uninstall 9.0.1-2 and
reinstall the 8.4 server?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 8.4 Data Not Compatible with 9.0.1 Upgrade?

[Carlos Mennens]

On Mon, Nov 1, 2010 at 12:45 PM, Bill Moran  wrote:
> I can't speak for Arch Linux' upgrade setup, but going from 8.4 -> 9.0
> requires that the data directory either be dumped/recreated, or ran
> through the new upgrade process (which (as yet) I have no experience
> with).
>
> If the Arch Linux stuff doesn't do that automatically, then you'll have
> to do it manually.

I just read:

http://www.postgresql.org/docs/9.0/static/release-9-0

Sadly I blindly upgraded my database w/o doing a dump / restore so can
anyone tell me if I am dead in the water or is there a way I can
recover from this error on my part?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] 8.4 Data Not Compatible with 9.0.1 Upgrade?

[Carlos Mennens]

I did an upgrade on my database server this past weekend and the
database fails to start. I checked /var/log/postgresql and found the
reason:

[r...@slave ~]# ps aux | grep postgres
root  5189  0.0  0.0   8128   956 pts/0S+   12:28   0:00 grep postgres

[r...@slave ~]# /etc/rc.d/postgresql start
:: Starting PostgreSQL

  [BUSY] server starting


  [DONE]
[r...@slave ~]# ps aux | grep postgres
root  5205  0.0  0.0   8128   960 pts/0R+   12:28   0:00 grep postgres

[r...@slave ~]# tail -n 50 /var/log/postgresql.log
FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 8.4,
which is not compatible with this version 9.0.1.
FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 8.4,
which is not compatible with this version 9.0.1.
FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 8.4,
which is not compatible with this version 9.0.1.

Does anyone know if this is a issue with PostgreSQL or with the way
Arch Linux packages the upgrade?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Adding a New Column Specifically In a Table

[Carlos Mennens]

On Wed, Oct 13, 2010 at 2:04 PM, Carlos Mennens
 wrote:
> OK so I have read the docs and Google to try and find a way to add a
> new column to an existing table. My problem is I need this new column
> to be created 3rd  rather than just dumping this new column to the end
> of my table. I can't find anywhere how I can insert my new column as
> the 3rd table column rather than the last (seventh). Does anyone know
> how I can accomplish this or if it's even possible. Seems like a
> common task but I checked the documentation and may have missed it in
> my reading.
>
>
> ALTER TABLE users ADD COLUMN employer VARCHAR(50) NOT NULL;

Ah sadly I just found this after I pressed 'send' and realized
PostgreSQL doesn't support it...that sucks :(

http://wiki.postgresql.org/wiki/Alter_column_position

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Adding a New Column Specifically In a Table

[Carlos Mennens]

OK so I have read the docs and Google to try and find a way to add a
new column to an existing table. My problem is I need this new column
to be created 3rd  rather than just dumping this new column to the end
of my table. I can't find anywhere how I can insert my new column as
the 3rd table column rather than the last (seventh). Does anyone know
how I can accomplish this or if it's even possible. Seems like a
common task but I checked the documentation and may have missed it in
my reading.


ALTER TABLE users ADD COLUMN employer VARCHAR(50) NOT NULL;

The above command dumps the 'employer' column at the very end of my
table which is not what I want.

Thanks for any assistance...

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general