Postgres Pro
Downloads
Home   >   mailing lists

Thread: dropping all databases and starting afresh

dropping all databases and starting afresh

From
Zoltan Szecsei
Date:
Hi,
Just joined the list, and this is first time foray into SQL databases, so apologies for the (most likely) lame questions.
I have installed 9.4 with PostGIS on Ubuntu 14.10 64bit Desktop.

After install I didn't like where the default databases went to, so I did:
    mkdir /mnt/geo_md1/postgres
    chown postgres:postgres /mnt/geo_md1/postgres
    su postgres
    postgres@gs01:/home/geograph$ createuser -s -d -e --replication zls
    postgres@gs01:/home/geograph$ pg_dropcluster –stop 9.4 main
    postgres@gs01:/home/geograph$ pg_createcluster --start -d /mnt/geo_md1/postgres 9.4 gisdata
After fiddling for a few days, I now want to start afresh, by deleting all data and creating a new area in a different directory, but:

neither:
postgres@gs01:/etc$ pg_dropcluster –-stop 9.4 gisdata
Usage: /usr/bin/pg_dropcluster [--stop] <version> <cluster>
nor
postgres@gs01:/etc$ pg_dropcluster –-stop 9.4 postgres
Usage: /usr/bin/pg_dropcluster [--stop] <version> <cluster>
works.
I've done no createdb commands, so dopdb shouldn't be what I want for this cleanup (I'd think).

Please can someone spot my oversight.
I'd like to cleanup, change the mount-point for my RAID, and then create a fresh start.

Thanks in advance,
Zoltan

postgres@gs01:/etc$ ls -la /mnt/geo_md1/postgres
total 84
drwx------ 18 postgres postgres 4096 Feb  2 18:07 .
drwxrwxrwx  4 staff    staff    4096 Jan 29 18:32 ..
drwx------  5 postgres postgres 4096 Jan 29 18:34 base
drwx------  2 postgres postgres 4096 Feb  1 14:13 global
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_clog
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_dynshmem
drwx------  4 postgres postgres 4096 Jan 29 18:34 pg_logical
drwx------  4 postgres postgres 4096 Jan 29 18:34 pg_multixact
drwx------  2 postgres postgres 4096 Feb  1 14:13 pg_notify
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_replslot
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_serial
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_snapshots
drwx------  2 postgres postgres 4096 Feb  1 14:24 pg_stat
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_stat_tmp
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_subtrans
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_tblspc
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_twophase
-rw-------  1 postgres postgres    4 Jan 29 18:34 PG_VERSION
drwx------  3 postgres postgres 4096 Jan 29 18:34 pg_xlog
-rw-------  1 postgres postgres   88 Jan 29 18:34 postgresql.auto.conf
-rw-------  1 postgres postgres  129 Feb  1 14:13 postmaster.opts



Re: dropping all databases and starting afresh

From
Craig James
Date:


On Mon, Feb 2, 2015 at 8:52 AM, Zoltan Szecsei <zoltans@geograph.co.za> wrote:
Hi,
Just joined the list, and this is first time foray into SQL databases, so apologies for the (most likely) lame questions.
I have installed 9.4 with PostGIS on Ubuntu 14.10 64bit Desktop.

After install I didn't like where the default databases went to, so I did:
    mkdir /mnt/geo_md1/postgres
    chown postgres:postgres /mnt/geo_md1/postgres
    su postgres
    postgres@gs01:/home/geograph$ createuser -s -d -e --replication zls
    postgres@gs01:/home/geograph$ pg_dropcluster –stop 9.4 main
    postgres@gs01:/home/geograph$ pg_createcluster --start -d /mnt/geo_md1/postgres 9.4 gisdata
After fiddling for a few days, I now want to start afresh, by deleting all data and creating a new area in a different directory, but:

neither:
postgres@gs01:/etc$ pg_dropcluster –-stop 9.4 gisdata
Usage: /usr/bin/pg_dropcluster [--stop] <version> <cluster>
nor
postgres@gs01:/etc$ pg_dropcluster –-stop 9.4 postgres
Usage: /usr/bin/pg_dropcluster [--stop] <version> <cluster>
works.
I've done no createdb commands, so dopdb shouldn't be what I want for this cleanup (I'd think).

Please can someone spot my oversight.
I'd like to cleanup, change the mount-point for my RAID, and then create a fresh start.

Destroying a database is easy if you are really are sure you don't want your data. Just kill Postgres, find the data directory, and delete everything.

Craig
 

Thanks in advance,
Zoltan

postgres@gs01:/etc$ ls -la /mnt/geo_md1/postgres
total 84
drwx------ 18 postgres postgres 4096 Feb  2 18:07 .
drwxrwxrwx  4 staff    staff    4096 Jan 29 18:32 ..
drwx------  5 postgres postgres 4096 Jan 29 18:34 base
drwx------  2 postgres postgres 4096 Feb  1 14:13 global
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_clog
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_dynshmem
drwx------  4 postgres postgres 4096 Jan 29 18:34 pg_logical
drwx------  4 postgres postgres 4096 Jan 29 18:34 pg_multixact
drwx------  2 postgres postgres 4096 Feb  1 14:13 pg_notify
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_replslot
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_serial
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_snapshots
drwx------  2 postgres postgres 4096 Feb  1 14:24 pg_stat
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_stat_tmp
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_subtrans
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_tblspc
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_twophase
-rw-------  1 postgres postgres    4 Jan 29 18:34 PG_VERSION
drwx------  3 postgres postgres 4096 Jan 29 18:34 pg_xlog
-rw-------  1 postgres postgres   88 Jan 29 18:34 postgresql.auto.conf
-rw-------  1 postgres postgres  129 Feb  1 14:13 postmaster.opts






--
---------------------------------
Craig A. James
Chief Technology Officer
eMolecules, Inc.
---------------------------------

Re: dropping all databases and starting afresh

From
Payal Singh
Date:
What error does pg_dropcluster give you?

Besides, agree with previous reply - easiest it to manually delete data directory.

Payal Singh,
Database Administrator,
OmniTI Computer Consulting Inc.
Phone: 240.646.0770 x 253

On Mon, Feb 2, 2015 at 2:12 PM, Craig James <cjames@emolecules.com> wrote:


On Mon, Feb 2, 2015 at 8:52 AM, Zoltan Szecsei <zoltans@geograph.co.za> wrote:
Hi,
Just joined the list, and this is first time foray into SQL databases, so apologies for the (most likely) lame questions.
I have installed 9.4 with PostGIS on Ubuntu 14.10 64bit Desktop.

After install I didn't like where the default databases went to, so I did:
    mkdir /mnt/geo_md1/postgres
    chown postgres:postgres /mnt/geo_md1/postgres
    su postgres
    postgres@gs01:/home/geograph$ createuser -s -d -e --replication zls
    postgres@gs01:/home/geograph$ pg_dropcluster –stop 9.4 main
    postgres@gs01:/home/geograph$ pg_createcluster --start -d /mnt/geo_md1/postgres 9.4 gisdata
After fiddling for a few days, I now want to start afresh, by deleting all data and creating a new area in a different directory, but:

neither:
postgres@gs01:/etc$ pg_dropcluster –-stop 9.4 gisdata
Usage: /usr/bin/pg_dropcluster [--stop] <version> <cluster>
nor
postgres@gs01:/etc$ pg_dropcluster –-stop 9.4 postgres
Usage: /usr/bin/pg_dropcluster [--stop] <version> <cluster>
works.
I've done no createdb commands, so dopdb shouldn't be what I want for this cleanup (I'd think).

Please can someone spot my oversight.
I'd like to cleanup, change the mount-point for my RAID, and then create a fresh start.

Destroying a database is easy if you are really are sure you don't want your data. Just kill Postgres, find the data directory, and delete everything.

Craig
 

Thanks in advance,
Zoltan

postgres@gs01:/etc$ ls -la /mnt/geo_md1/postgres
total 84
drwx------ 18 postgres postgres 4096 Feb  2 18:07 .
drwxrwxrwx  4 staff    staff    4096 Jan 29 18:32 ..
drwx------  5 postgres postgres 4096 Jan 29 18:34 base
drwx------  2 postgres postgres 4096 Feb  1 14:13 global
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_clog
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_dynshmem
drwx------  4 postgres postgres 4096 Jan 29 18:34 pg_logical
drwx------  4 postgres postgres 4096 Jan 29 18:34 pg_multixact
drwx------  2 postgres postgres 4096 Feb  1 14:13 pg_notify
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_replslot
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_serial
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_snapshots
drwx------  2 postgres postgres 4096 Feb  1 14:24 pg_stat
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_stat_tmp
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_subtrans
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_tblspc
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_twophase
-rw-------  1 postgres postgres    4 Jan 29 18:34 PG_VERSION
drwx------  3 postgres postgres 4096 Jan 29 18:34 pg_xlog
-rw-------  1 postgres postgres   88 Jan 29 18:34 postgresql.auto.conf
-rw-------  1 postgres postgres  129 Feb  1 14:13 postmaster.opts






--
---------------------------------
Craig A. James
Chief Technology Officer
eMolecules, Inc.
---------------------------------

Re: dropping all databases and starting afresh

From
Jorge Torralba
Date:
easiest thing I can think of is :

save posgtesql.conf
save pg_hba.conf
pg_dumpall
shut down
set PGDATA to new location you defined
initdb -D /your/new/data/dir
cp old postgresql.conf and pg_hba.conf to new datadir
pg_ctl start -D /your/new/data/dir
psql -f dumpfile postgres

validate everything then rm -rf old dtadir.

On Mon, Feb 2, 2015 at 11:34 AM, Payal Singh <payal@omniti.com> wrote:
What error does pg_dropcluster give you?

Besides, agree with previous reply - easiest it to manually delete data directory.

Payal Singh,
Database Administrator,
OmniTI Computer Consulting Inc.
Phone: 240.646.0770 x 253

On Mon, Feb 2, 2015 at 2:12 PM, Craig James <cjames@emolecules.com> wrote:


On Mon, Feb 2, 2015 at 8:52 AM, Zoltan Szecsei <zoltans@geograph.co.za> wrote:
Hi,
Just joined the list, and this is first time foray into SQL databases, so apologies for the (most likely) lame questions.
I have installed 9.4 with PostGIS on Ubuntu 14.10 64bit Desktop.

After install I didn't like where the default databases went to, so I did:
    mkdir /mnt/geo_md1/postgres
    chown postgres:postgres /mnt/geo_md1/postgres
    su postgres
    postgres@gs01:/home/geograph$ createuser -s -d -e --replication zls
    postgres@gs01:/home/geograph$ pg_dropcluster –stop 9.4 main
    postgres@gs01:/home/geograph$ pg_createcluster --start -d /mnt/geo_md1/postgres 9.4 gisdata
After fiddling for a few days, I now want to start afresh, by deleting all data and creating a new area in a different directory, but:

neither:
postgres@gs01:/etc$ pg_dropcluster –-stop 9.4 gisdata
Usage: /usr/bin/pg_dropcluster [--stop] <version> <cluster>
nor
postgres@gs01:/etc$ pg_dropcluster –-stop 9.4 postgres
Usage: /usr/bin/pg_dropcluster [--stop] <version> <cluster>
works.
I've done no createdb commands, so dopdb shouldn't be what I want for this cleanup (I'd think).

Please can someone spot my oversight.
I'd like to cleanup, change the mount-point for my RAID, and then create a fresh start.

Destroying a database is easy if you are really are sure you don't want your data. Just kill Postgres, find the data directory, and delete everything.

Craig
 

Thanks in advance,
Zoltan

postgres@gs01:/etc$ ls -la /mnt/geo_md1/postgres
total 84
drwx------ 18 postgres postgres 4096 Feb  2 18:07 .
drwxrwxrwx  4 staff    staff    4096 Jan 29 18:32 ..
drwx------  5 postgres postgres 4096 Jan 29 18:34 base
drwx------  2 postgres postgres 4096 Feb  1 14:13 global
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_clog
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_dynshmem
drwx------  4 postgres postgres 4096 Jan 29 18:34 pg_logical
drwx------  4 postgres postgres 4096 Jan 29 18:34 pg_multixact
drwx------  2 postgres postgres 4096 Feb  1 14:13 pg_notify
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_replslot
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_serial
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_snapshots
drwx------  2 postgres postgres 4096 Feb  1 14:24 pg_stat
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_stat_tmp
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_subtrans
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_tblspc
drwx------  2 postgres postgres 4096 Jan 29 18:34 pg_twophase
-rw-------  1 postgres postgres    4 Jan 29 18:34 PG_VERSION
drwx------  3 postgres postgres 4096 Jan 29 18:34 pg_xlog
-rw-------  1 postgres postgres   88 Jan 29 18:34 postgresql.auto.conf
-rw-------  1 postgres postgres  129 Feb  1 14:13 postmaster.opts






--
---------------------------------
Craig A. James
Chief Technology Officer
eMolecules, Inc.
---------------------------------




--
Thanks,

Jorge Torralba
----------------------------

Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwise use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.

Re: dropping all databases and starting afresh

From
Matheus de Oliveira
Date:

On Mon, Feb 2, 2015 at 2:52 PM, Zoltan Szecsei <zoltans@geograph.co.za> wrote:
neither:
postgres@gs01:/etc$ pg_dropcluster –-stop 9.4 gisdata
Usage: /usr/bin/pg_dropcluster [--stop] <version> <cluster>
nor
postgres@gs01:/etc$ pg_dropcluster –-stop 9.4 postgres
Usage: /usr/bin/pg_dropcluster [--stop] <version> <cluster>
works.
I've done no createdb commands, so dopdb shouldn't be what I want for this cleanup (I'd think).

If you copied+pasted the exact command you run, then no wonder it is wrong. If you look closer you are not calling "--stop", the first dash is another character (not minus/hyphen character). One is "EN DASH" with code 0xe28093 [1], while the correct one is "HYPHEN-MINUS" with code 0x2d [2].

Also, execute pg_lsclusters to see what clusters you actually have on this instance, then you can use the version and cluster name provided there to call pg_dropcluster.

[1] http://www.decodeunicode.org/en/u+2013/properties
[2] http://www.decodeunicode.org/en/u+002D/properties

Best regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres

Re: dropping all databases and starting afresh

From
Zoltan Szecsei
Date:

On 02/04/2015 12:07 PM, Matheus de Oliveira wrote:

On Mon, Feb 2, 2015 at 2:52 PM, Zoltan Szecsei <zoltans@geograph.co.za> wrote:
neither:
postgres@gs01:/etc$ pg_dropcluster –-stop 9.4 gisdata
Usage: /usr/bin/pg_dropcluster [--stop] <version> <cluster>
nor
postgres@gs01:/etc$ pg_dropcluster –-stop 9.4 postgres
Usage: /usr/bin/pg_dropcluster [--stop] <version> <cluster>
works.
I've done no createdb commands, so dopdb shouldn't be what I want for this cleanup (I'd think).

If you copied+pasted the exact command you run, then no wonder it is wrong. If you look closer you are not calling "--stop", the first dash is another character (not minus/hyphen character). One is "EN DASH" with code 0xe28093 [1], while the correct one is "HYPHEN-MINUS" with code 0x2d [2].

AWESOME !!
Well spotted.
I must have copy&pasted it from one of the online pdf manuals.

In the end I just rm -rf'd the files, as suggested by others on this list. I was initially concerned about that simple approach, as I figured that many postgres conf files will still point to that deleted area - but as postgres will not run again until I do an initdb -D command, it (should) goes to follow that those conf files will then be correctly updated.

Big thanks to all who contributed.

Kind regards,
Zoltan



Re: dropping all databases and starting afresh

From
Matheus de Oliveira
Date:

On Wed, Feb 4, 2015 at 9:23 AM, Zoltan Szecsei <zoltans@geograph.co.za> wrote:
In the end I just rm -rf'd the files, as suggested by others on this list. I was initially concerned about that simple approach, as I figured that many postgres conf files will still point to that deleted area - but as postgres will not run again until I do an initdb -D command, it (should) goes to follow that those conf files will then be correctly updated.

Yeah, I understand your concern, and I do agree. On installation from packages on Debian/Ubuntu-like, you should keep with the cluster management tools provided, like pg_createcluster instead of initdb, pg_ctlcluster instead of pg_ctl, pg_dropcluster instead of rm, and so on.

Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres

Re: dropping all databases and starting afresh

From
Zoltan Szecsei
Date:
OK - cool - thanks for the tips.
Regards,
Zoltan
On 02/04/2015 02:02 PM, Matheus de Oliveira wrote:

On Wed, Feb 4, 2015 at 9:23 AM, Zoltan Szecsei <zoltans@geograph.co.za> wrote:
In the end I just rm -rf'd the files, as suggested by others on this list. I was initially concerned about that simple approach, as I figured that many postgres conf files will still point to that deleted area - but as postgres will not run again until I do an initdb -D command, it (should) goes to follow that those conf files will then be correctly updated.

Yeah, I understand your concern, and I do agree. On installation from packages on Debian/Ubuntu-like, you should keep with the cluster management tools provided, like pg_createcluster instead of initdb, pg_ctlcluster instead of pg_ctl, pg_dropcluster instead of rm, and so on.

Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres