From: | Ron St-Pierre <ron(dot)pgsql(at)shaw(dot)ca> |
---|---|
To: | Craig White <craigwhite(at)azapple(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: getting list of tables from command line |
Date: | 2007-10-31 19:22:42 |
Message-ID: | 4728D602.7050907@shaw.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Craig White wrote:
> I wrote a little script to individually back up table schemas, table
> data and then vacuum the table and it works nicely but I wanted a way to
> query a database and get a text file with just the table names and
> cannot figure out a way to do that.
>
> my script looks like this...
> (all I want is to get a list of the tables into a text file pg_tables)
>
> #/bin/sh
> #
> DB_NAME=whatever
> #
> for i in `cat pg_tables`
> do
> pg_dump --username=postgres \
> --schema=db
> --table=$i \
> --schema-only \
> $DB_NAME > schemas/$i.sql
> pg_dump --username=postgres \
> --schema=db \
> --table=$i \
> --data-only \
> $DB_NAME > data/$i.sql
> vacuumdb --username=postgres \
> --dbname=$DB_NAME \
> --table=db.$i \
> --verbose \
> --full
> done
>
> Is there a way to do that?
>
>
From the command line you can run:
psql mydbname -c "\d"
to get a list of all public table names.
Or just select the table names from pg_tables and send it to a file:
psql myDBname -c "SELECT tablename FROM pg_tables WHERE schemaname =
'someschema';" > my_tables.txt
This works on my 8.1 database on RHEL. You could also use something
similar inside of your script to generate the table names and send them
to pg_dump. As far as I know, if you specify a table name, but don't
specify a schema name to pg_dump, it will automatically dump all tables
with that name, irregardless of what schema they belong to. I'm sure
someone will let me know if that's not correct.......
hth
Ron
> Craig
>
> PS there's a lack of cohesion between various commands such as vacuumdb
> and pg_dump for things like '--schema'
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Douglas McNaught | 2007-10-31 19:50:10 | Re: Securing stored procedures and triggers |
Previous Message | Scott Marlowe | 2007-10-31 18:36:41 | Re: Replacing RDBMS |