[WYLUG-help] PostgreSQL and SQL-Ledger

[Mike Goodman]

When I tried the dropdb command on template1, so I could just try to
start again with sql-ledger, I got an error message to the effect of
fail because it could not delete a running database. I could not find
out how to stop it running. Which is why I went to the trouble of
uninstalling the whole package. I thought I'd managed to delete any
other databases and users - but was told "user blah exists" as part of
the error message when I tried to recreate one.

I just attempted to find out what databases exist thus:

postgres at server:~$ psql \l
psql: FATAL:  database "l" does not exist

whereas without psql in front, it just says l no such command.

Sarge is installing v7.4 which is now dated. The current stable version
is 8.1. There's also an old version of sql-ledger in the Sarge
repository but I didn't bother with that at all.

Does anyone have any experience of installing postgresql with apt? Does
it complete the post-install routines including setting paths correctly?
The reason I ask is because as user postgres commands like pg_ctl are
simply not recognised, yet are the standard way of doing things
according to the manual. Also, the data directories are not as in the
manual's quoted paths.

How do I start again? Would purge do it? I'd need to be reasonably sure
of getting rid of all the old stuff. Then I think I'd probably be better
off installing PostgreSQL-8.1 from source and following the post-install
routines correctly, then unpacking the SQL-Ledger tarball again. At
least I'd have a reasonable idea of where everything came from.

However, will a build from source sit well upon an apt-installed distro,
and will the PHP pgsql module work with it? Further, how can I be sure
I've cleared out all the old malfunctioning scripts before I start?

The alternative would be to rescue what I've got, and I don't even know
where to start on that path. Any ideas, anyone, without me having to do
a long sysadmin course first?

On Sat, 2006-06-03 at 20:49 +0100, Stephen wrote:
> On 03 Jun 06, Mike Goodman (mike.goodman at zen.co.uk) wrote:
> > I assumed that would have rid me of the earlier pgsql user I had created
> > and all of the databases would have gone. No such thing. Trying to
> > create a new dataset produces the error:
> > 
> > Error!
> > CREATE SEQUENCE id start 10000
> > ERROR: relation "id" already exists
> 
> Sounds like it left the old database in when you removed it.
> 
> The postgres command line (psql) should let you delete (drop) the database, 
> \l  - list databases available
> \c  - connect to another database
> \d  - by itself, list tables. With a table name, describe the table
>  
> > Can anyone tell me how I can find out who or what owns sequence id start
> > 10000 and if I can delete it? I'm assuming it's something to do with
> > pgsql
> 
> The sequence is a counter for a column of autonumbering IDs, so it'll be
> specific to 1 table.
> 
> Postgres normally leaves databases behind when you uninstall it, though it can
> be reconfigured to delete them with dpkg-reconfigure.
>