Re: [GENERAL] getting list of tables from command line

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
>
>

---------------------------(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