Re: getting list of tables from command line

On 31 out, 12:01, craigwh(dot)(dot)(dot)(at)azapple(dot)com (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?
>
> 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

First you can create a file with the SQL statement:

select tablename from pg_tables where schemaname not in
('information_schema','pg_catalog' ) order by tablename;

After, run it by psql:

psql -U [postgres_user] -d [database_name] -f [file_created_with_SQL]
> [output_file]

This will dump all non-database schema tables into the output file, so
you can open it and read table names.